11 Replies Latest reply on Aug 1, 2016 2:12 AM by Mike_Mitchell

    DateField Calculation End of Year

    PaSav-ICT

      I'm working with a calculation to calculate amount of days.

       

      I have a

      - contract start date, this is a date field

      - contract end date, this is a date field

      - end date for the calculation, this is a calculation that say's and is also set as result is date :

           Case(IsEmpty(contract  end date); Date(12;31;Year(Get(CurrentDate);contract end date)

       

       

      The meaning is that the end date calculation gifs me ore contract end date if filled in, ore the last day of the current year.

       

       

      But this seams not to work, what do i think wrong in this calculation.

       

      Message was edited by: Patrick Savelberg, made a type error in this message. My formula was correct in the database.

       

      Message was edited by: Patrick Savelberg second time : And i forgot what the problem was, when i use the end date by calculation it is not giving me the number of days. So, end date of the calculation - contract start date is giving me -736100 as answer. But when i do contract end date - contract start date, i get the right number of days. So why is the end date by calculation not working ?

        • 1. Re: DateField Calculation End of Year
          Mike_Mitchell

          The Date function uses its parameters in the US standard of month / day / year, so you would need:

           

          Case (

               IsEmpty ( contract end date ) ;

               Date ( 12 ; 31 ; Year ( Get ( CurrentDate ))) ;

               contract end date

          )

           

          (As an aside: The number of parentheses in your original is incorrect, but FileMaker would alert you to that.)

           

          HTH


          Mike

          • 2. Re: DateField Calculation End of Year
            beverly

            AFAIK the Date() function needs "month; day ; year" regardless of international versions:

             

            You can change the 'en' to a common language such as 'fr' or 'es' or 'de' or ... in the above link to verify.

            beverly

            • 3. Re: DateField Calculation End of Year
              PaSav-ICT

              My mistake, in the post i made the type error, in the calculation i have used the correct format.

               

              But the result is stil not what i expect.

              • 4. Re: DateField Calculation End of Year
                PeterDoern

                Is the calculated End Date field is set to return a Date value?

                • 5. Re: DateField Calculation End of Year
                  philmodjunk

                  I suggest posting the expression where you calculate the number of days (appears the field referenced in the first term is empty to give you that large negative number)

                   

                  I suggest you explain which of several setups are being used to actually calculate that difference in days.

                   

                  and whether these are expressions in calculation fields or auto enter calculations.

                  • 6. Re: DateField Calculation End of Year
                    PaSav-ICT

                    Yes i set the output of the calculation as a date. So that should work.

                    • 7. Re: DateField Calculation End of Year
                      PaSav-ICT

                      The field contract as calculation is working like i should be. So this is never empty. Or it contains the contract end date or it is giving me the last date of the current year.

                       

                      So i have the next fields :

                       

                      - StartDateContract, date field

                      - EndDateContract, date field

                      - EndDateCalculation, calculation with type Date, to give me or the EnDateContract or the last day of current year.

                           using this calculation :

                                Case(

                                IsEmpty(ContractEndDate); Date (12;31;Year(Get(CurrentDate);

                                ContractEndDate

                                )

                      - NumberWorkDays, calculation type number, (EndDateCalculation-StartDateContract)+1 should give me a number of days working in current year if startdate is also in the current year.

                       

                      But it gives me :

                           - if EndDateContract is empty the value -736328

                           - if EndDateContract contains a date that is after startdate the number 1

                         

                      In both Cases the field EndDateCalculation gives me the right date.

                      • 8. Re: DateField Calculation End of Year
                        Mike_Mitchell

                        PaSav-ICT wrote:

                         

                        - NumberWorkDays, calculation type number, (EndDateCalculation-StartDateContract)+1 should give me a number of days working in current year if startdate is also in the current year.

                         

                        But it gives me :

                        - if EndDateContract is empty the value -736328

                        - if EndDateContract contains a date that is after startdate the number 1

                         

                        What value is in StartDateContract? Is it a date? Is EndDateCalculation also a date?

                         

                        Note: Simply subtracting dates will not give you the number of working days. It just gives you the number of days. If you want working days, you'll need to use something like this Custom Function:

                         

                        /*

                        cfWorkingDays adapted from Barbecue, Le Kastelo de Barbakoa

                        cfWorkingDaysBetweenDates ( start ; end ; saturday )

                         

                        Returns the number of working days between the start and end dates

                        Returns a negative number if start is earlier than end

                        Passing a "y" in saturday indicates that saturday is a working day

                         

                        */

                         

                        Let ( [

                         

                        startdate = If ( start ≤ end ; start ; end ) ;

                        enddate = If ( start ≤ end ; end ; start ) ;

                        weekEnd = If ( saturday = "y" ; 8 ; 7 ) ;

                        startDay = DayOfWeek ( startdate ) ;

                         

                        days = Case (

                          startdate < enddate ;

                          ( startDay > 1 and startDay < weekEnd ) + cfWorkingDaysBetweenDates ( startdate + 1 ; enddate ;  saturday ) ;

                          0

                        )

                         

                        ] ;

                         

                        If ( start ≤ end ; days ; 0 - days )

                         

                        )

                        • 9. Re: DateField Calculation End of Year
                          PaSav-ICT

                          Thanks Mike, but my calculations gives me the info i need. Because it needs also the weekends in the calculation. Only the calculation is not working and i can't see the problem, all the info is there. But seems that the Calculated date is not seen as a date. How to fix this ?

                          • 10. Re: DateField Calculation End of Year
                            Mike_Mitchell

                            Is the result of the calculation a date?

                             

                            Did you try GetAsDate ( {result} )?

                            • 11. Re: DateField Calculation End of Year
                              Mike_Mitchell

                              And I'm not sure why you would need a number of days reflected as a date?