11 Replies Latest reply on May 13, 2013 4:24 AM by Mike_Mitchell

    Calculating 60 days ago

    KathyMacy

      Does someone have a calulation or function to be used in a script for 60 days ago from today's date?

       

      Thanks,

      Kathy

        • 1. Re: Calculating 60 days ago
          deninger

          How about simply:

           

          Get (CurrentDate) - 60

           

          Returns 3/11/2013

          • 2. Re: Calculating 60 days ago
            taylorsharpe

            FileMaker is very smart in handling date calculations. 

             

            Date (  1  ; 1 ; 2013 ) - 60  returns 11/2/2012

             

            Date ( 1 ; 1 - 60 ; 2013 )    also returns 11/2/2012

             

            And you can use other date calculations or functions like deninger showed you with Get ( CurrentDate ). 

             

            Technically, all date fields are stored instally in FileMaker as the number of days since the date 1/1/0001.  FileMaker then just displays dates in the date format we are used to.  But in reality, it thinks of each date as a number that is the number of days since 1/1/0001.  For example, today is 5/10/2013, but this is really stored in FileMaker as 734998.  If you multiply any date by 1 and set the calculation engine to show the output in Number, you will see the numerical value of a date.  Just go into Date View and type in "Get ( CurrentDate ) * 1" and you'll see today's number. 

            • 3. Re: Calculating 60 days ago
              BruceHerbach

              You can also use the getasnumber() function to return the numeric value. 

              • 4. Re: Calculating 60 days ago
                Mike_Mitchell

                Keep in mind, too, that if you don't want a literal 60 days, but rather "the same date two months ago", you can use something like:

                 

                Let ( [

                todaysDate = Get ( CurrentDate ) ;

                currentDay = Day ( todaysDate ) ;

                currentMonth = Month ( todaysDate ) ;

                currentYear = Year ( todaysDate ) ] ;

                 

                Date ( currentDay ; currentMonth - 2 ; currentYear )

                 

                )

                 

                Mike

                • 5. Re: Calculating 60 days ago
                  taylorsharpe

                  Mike, I know that there are different date formatting around the world, which will probaby explain the difference and probably adjustable in the OS.  However, for your formula, the normal way I would format it here in the USA is:

                   

                            Date ( currentMonth - 2 ; currentDay ; currentYear )

                  • 6. Re: Calculating 60 days ago
                    Mike_Mitchell

                    ACK! You're right. Brain fade. Sorry!  

                    • 7. Re: Calculating 60 days ago
                      raybaudi

                      KathyMacy ha scritto:

                       

                      Does someone have a calulation or function to be used in a script for 60 days ago from today's date?

                       

                      If you need a script to find records between today and 60 days ago, you could start with:

                       

                      Set Error Capture [ on ]

                      Enter Find Mode [ ]

                      Set Field [ YourTable::YourDateField ; Get ( Current Date ) - 60 & "..." & Get ( Current Date ) ]

                      Perform Find

                      ...

                      • 8. Re: Calculating 60 days ago
                        rogerengstrom

                        For some reason that behaves a bit odd when calculating back to februari with 28 days.

                        Doing a numeric calculation on the date seems safer.

                         

                        Example

                        Let ( [

                        todaysDate = Date ( 04 ; 30 ; 2013 ) ;

                        currentDay = Day ( todaysDate ) ;

                        currentMonth = Month ( todaysDate ) ;

                        currentYear = Year ( todaysDate ) ] ;

                         

                        Date ( currentMonth - 2 ; currentDay ; currentYear )

                        & "¶" &

                        todaysDate - 60

                        )

                         

                        Result:

                        3/2/2013 (Subtracting months)

                        3/1/2013 (subtracting days)

                        • 9. Re: Calculating 60 days ago
                          Mike_Mitchell

                          I'm not sure I agree that it's "safer" to subtract 60 days. How is 3/1 "better" than 3/2? Neither is equal to 2/30 (which doesn't exist); you still get an inaccurate result. Plus, if you just subtract 60 days, you will much more frequently run into vagaries when there are 31 days in a month. Example: If I back up from June 1 and subtract 60 days, I wind up on April 2.

                           

                          You do bring up the point that we have to be concerned about what happens at the end of a month. This is not only a concern in February, but in months with 31 days as well. For example, what happens if I try to back up from August 31? Whether I use the Date calculation or the minus 60 calculation, I don't get the right answer.

                           

                          In such a case, we probably would need to test to make sure our resultant month is actually the source month minus 2 and make adjustments. You can accomplish this using:

                           

                          Date ( month ; 0 ; year )

                           

                          This will cause FileMaker to use the last date of month - 1.

                           

                          Of course, all this is predicated on the assumption that the OP needs to have the original date minus 2 months instead of minus 60 days. Those are two different questions (as this discussion points out). If it's a literal 60 days, then the point is moot.

                           

                          Mike

                          • 10. Re: Calculating 60 days ago
                            taylorsharpe

                            Here is a formula I use when I want 2 months back, but I do not want the two months back to roll into the next month.  So if I have April 30th, there is no February 30th, but such a date will actually show up as March 2nd.  Often I want a April 30th date minus 2 months to show the last day of February which would be February 28th.  This formula will do that:

                             

                             

                             

                            Let ( [

                             

                             

                            F1 = Get ( CurrentDate ) ;

                             

                             

                            MonthsBack = 2 ;

                             

                             

                            MM = Month ( F1 ) ;

                            DD = Day ( F1 ) ;

                            YY = Year ( F1 ) ;

                             

                             

                            NewDate = Date ( MM - MonthsBack ; DD ; YY ) ;

                             

                             

                            LastDayOfMonth = Date ( MM - MonthsBack + 1 ; 0 ; YY ) ;

                             

                             

                            Result = If ( NewDate > LastDayOfMonth ; LastDayOfMonth ; NewDate )

                             

                             

                            ] ;

                             

                             

                            Result

                             

                             

                            )

                            • 11. Re: Calculating 60 days ago
                              Mike_Mitchell

                              Yeah ... like that.