10 Replies Latest reply on Aug 5, 2017 7:44 AM by fmpdude

    Calculate date

    cmj

      I'm attempting to calculate the date of the last occurrence of a given week day (based on date1) that falls prior to date in the future.

      For example: If date1 = August 15, 2017  - What is the date of the last week day (that's equal to the week day of August 15, 2017) prior to June 12, 2020 ?

      The answer in this example is June 9, 2020. Simple to answer when looking at a calendar, but I just can't get my brain around calculating this.

      Thanks

        • 1. Re: Calculate date
          philmodjunk

          Let ( [ d1 = YourFirstDateHere ;

                    d2 = YourFutureDateHere ] ;

                    d2 - ( DayOfWeek ( d2 ) - DayOfWeek ( d1 ) )

                 )

          • 2. Re: Calculate date
            cmj

            Your calculation is brilliant!

            Thank you.

            • 3. Re: Calculate date
              fmpdude

              With date math, I often go back to Java and use a microservice to do the heavy lifting.

               

              Based on Phil's nice formula, doesn't appear to be any need for Java here!


              Here's what I did in Java, anyway, that I would usually integrate with an FMP app (it's the same basic idea, just object oriented and using the extremely powerful Java 8 Date API). It's really only the last line of code, which is more or less the same as what Phil did that does the calc.

               

              // today's weekday
              LocalDate todayWeekDay LocalDate.of(2017, Month.AUGUST, 15) ;
              int todayDateNumber = todayWeekDay.getDayOfWeek().getValue();
              // future date.
              LocalDate futureDate = LocalDate.of(2020, Month.JUNE, 12) ;
              int futureDateDayNumber = futureDate.getDayOfWeek().getValue();
              System.out.println("today's date: " + todayWeekDay);
              System.out.println("future date: " + futureDate);
              // Last weekday in future that matches today's weekday.
              LocalDate targetDate =
                   futureDate.minusDays(Math.abs(todayDateNumber-futureDateDayNumber)) ;
              System.out.println("Final Last WeekDay with the same day In future before " +
                   futureDate + " is: " + targetDate);

              ------

               

              OUTPUT:

              today's date: 2017-08-15

              future date: 2020-06-12

              Final Last WeekDay with the same day In future before 2020-06-12 is: 2020-06-09

              • 4. Re: Calculate date
                cmj

                Thank you for your Java example. I think I will stick to FM for the time being though.

                • 5. Re: Calculate date
                  philmodjunk

                  For some reason, I'm thinking of a stick of dynamite... (inside joke)

                  • 6. Re: Calculate date
                    cmj

                    Re:

                    Let ( [ d1 = YourFirstDateHere ;

                              d2 = YourFutureDateHere ] ;

                              d2 - ( DayOfWeek ( d2 ) - DayOfWeek ( d1 ) )

                           )

                    Try a start date of June 1, 2017 and end date of June 8, 2020

                    I'm getting June 11, 2020 as the result, when the correct answer is June 4, 2020 (the last Thursday before June 8, 2020).

                    With start dates up to and including June 10, 2017, the result is 1 week greater than the correct date.

                    Once I use June 11, 2017 start date, the result is correct at June 7, 2020. (when using June 8, 2020 as the end date).

                    Any ideas as to why this happens?

                    Thanks

                    • 7. Re: Calculate date
                      philmodjunk

                      Took me a few minutes of looking at the day of week values to see it. My original calc only works if the day of week value for d2 is greater than the day of week value for d1.

                       

                      Let ( [ d1 = YourFirstDate ;

                              d2 = YourFutureDate ;

                              dw1 = dayofweek ( d1 ) ;

                              dw2 = dayofweek ( d2 ) ] ;

                       

                              d2 - ( dw2 - Dw1 ) - 7 * ( dw1 > dw2 )

                       

                             )

                      • 8. Re: Calculate date
                        cmj

                        Excellent!

                        Thank you very much

                        • 9. Re: Calculate date
                          fmpdude

                          Good one!

                           

                          At least you spelled it right this time (inside joke)!

                          • 10. Re: Calculate date
                            fmpdude

                            I love date math problems!

                             

                            In this case, I would agree that FMP is sufficient.

                             

                            But, remember the power of "Abstraction". This code would be abstracted (hidden) in a service FMP would call. The user would never see it. Also, that service could be used by any FMP application running, and any other HTTP-verb-enabled application (Terminal, Browser, ....).

                             

                            Thus, the service approach for FMP also gives you a "global library" and don't need to worry about configuring CFs from FMP app to FMP app. And, in general, you can create logic you'd have to pay big bucks for with a plug-in. All for free.

                             

                            Finally, when using a professional IDE, you get amazing productivity tools - like pop-ups for method selection (this is object-oriented code, after all), and real-time-compilation as-you-type (a thousand other things I won't list here, too)!  A professional IDE makes creating code like I posted straightforward. And, the Java code is readable and step-by-step debug-able (no tools in FMP to debug a CF--that chore unceremoniously dumped in your lap). And the libraries already extensively tested. (And, in this case, the Java Date API is so rich, there's little you can't do with it. Including creating your own Temporal TimeAdjusters)

                             

                            ------

                             

                            In any case, I updated my code below to handle your new date case. I didn't consider the case where the nearer week day number would be greater than the future week day number.

                             

                            I just added an "if" statement (and removed the absolute value call) for that logic.

                             

                            Note that below with a date object, we automatically have methods (functions), like "minusDays()", built right into the object itself you can use. That's OO! Don't need to search "over there" for some function to use "over here" in a non-OO environment. Encapsulation. It's great!

                            .

                            .

                            .

                            if (soonerDayNumber <= futureDayNumber// DOW number: 2 = Tuesday, for example
                            {

                               targetDate = futureDate.minusDays(futureDayNumber - soonerDayNumber);

                            }

                            else
                            {

                               targetDate = futureDate.minusDays(7 - (soonerDayNumber - futureDayNumber));

                            }

                             

                            (This code runs amazingly fast, too.)

                             

                            ----


                            Here are the updated outputs:

                            today's date: 2017-06-01

                            future date: 2020-06-08

                            Final Last WeekDay with the same day In future before 2020-06-08 is: 2020-06-04

                             

                            --

                            today's date: 2017-08-15

                            future date: 2020-06-12

                            Final Last WeekDay with the same day In future before 2020-06-12 is: 2020-06-09