14 Replies Latest reply on Apr 17, 2017 12:21 PM by garry107

    Printing the range of two dates


      Hey all,


      I have two fields with dates. I want to print all the years in both of the fields in a third field.

      eg : field 1: 03/22/2016

             field 2: 05/22/2022

         I want field 3: to show 2016,2017,2018,2019,2020,2021,2022


      How can this be achieved




        • 1. Re: Printing the range of two dates
          Johan Hedman

          I would search at briandunning.com for a Custom Function that does what you want

          FileMaker Pro Custom Functions


          To be able to work with Custom Functions you need FileMaker Pro Advanced

          • 2. Re: Printing the range of two dates

            If you don't have Advanced, a looping script can produce these dates.

            • 3. Re: Printing the range of two dates

              If you don't have Advanced, a simple problem like your posted question could be difficult.


              WITH "Advanced, this is an extremely simple problem to solve.


              As Phil suggested, a simple looping script with a couple variables is all you need. Note, that I took a little extra care to make sure there wasn't a trailing comma.


              This is a very quick example (literally about 8 minutes) with no code documentation (a no-no) and almost no testing (another no-no) so there could be some issues I'm not considering. One potential issue is that  it's "Assumed" that the fields will be in ascending order in the code below. That "assumption" should also be checked in the script and correctly handled (not for an error reported back to the user!) so you still get the right range of years. The script should also check for empty fields and other potential boundary conditions.


              In any case, this should give you some ideas.


              HOPE THIS HELPS.

              • 4. Re: Printing the range of two dates

                The situation you describe is essentially the same as this one but you only want the years to increment and not the days.


                The possible solutions take the same form.


                Other than a custom function or a scripted solution, you could also achieve the result with a direct calculation.


                // list years between field 1 and field 2

                // This formula accommodates 7 years.  Add more lines to add more years.


                [startYear = Year(field 1);

                endYear = Year(field2);

                d = endYear - startYear


                startDate &

                If(d>1; "," & GetAsDate(startYear+1);"") &

                If(d>2; "," & GetAsDate(startYear+2);"") &

                If(d>3; "," & GetAsDate(startYear+3);"") &

                If(d>4; "," & GetAsDate(startYear+4);"") &

                If(d>5; "," & GetAsDate(startYear+5);"") &

                If(d>6; "," & GetAsDate(startYear+6);"") &

                // Add additional lines here copying the format above to the maximum you need.

                If(d>0; "," & endYear)



                Or you can make a more complicated calculation that is essentially unlimited in its ability to list years.

                The CustomList() function by Agnès Barouh does not require recursion and can be used as a regular calculation to generate an arbitrarily long list of years.


                To convert it to a non-Custom Function calculation, wrap it with a Let() and define the three parameter values as Let() variables.

                Since the CustomList() function returns a ¶ separated list of values, wrap that in Substitute() to use commas instead of carriage returns.





                Start = Year(field 1);

                End = Year(field 2);

                Function = "[n]"


                ... the full original CustomList() definition goes here


                "¶"; ",")


                • 5. Re: Printing the range of two dates

                  This make lines dynamically (max about 400 years)


                  Let ([

                  startY = Year ( field1 ) ;

                  endY = Year ( field2 ) ;

                  expr = "Y=" & startY & ";result=Y" & Case ( endY > startY ; Substitute ( 10 ^ ( endY - startY ) - 1 ; 9 ; ";Y=Y+1;result=result&"",""&Y" ) )


                  Evaluate (

                  "Let([" & expr & "];result)"



                  2 of 2 people found this helpful
                  • 6. Re: Printing the range of two dates

                    Mr. OP,


                    Please respond if any of the suggested approaches here meets your needs or if you have additional questions, OK?


                    Multiple folks have now posted, but we have gotten no feedback from you if any of the suggested approaches fixes your issue.



                    • 7. Re: Printing the range of two dates

                      I tried this but the field is showing 2.0172e + 27 instead of years but when I click on the field its showing all the years as per my requirement. How should I rectify that?




                      • 8. Re: Printing the range of two dates

                        Gary, is the calculation set to show TEXT or number?


                        1 of 1 people found this helpful
                        • 9. Re: Printing the range of two dates

                          My bad. Changed to Text.




                          • 10. Re: Printing the range of two dates

                            That code will probably work (I haven't tested it), but it's difficult to read and it will be difficult to maintain. I'm not finding fault with the author of the code or the quality of its results, just from a code-review perspective, it is difficult looking code.


                            Also, it appears to have a limitation: "This make lines dynamically (max about 400 years)"


                            In this case, I would use a script example, like the one I posted wrote, or better yet, add a quick method to a micro-service.


                            In Java, what you're trying to do is literally ONE LINE OF CODE and it's declarative not imperative (declarative => you define "what" you want to do (as with SQL) without having to specify "how" to do it, as in scripts or code).


                            And, we can just specify we want a "Set" as the output so we can easily eliminate duplicate years -- see example below.


                            Assume you have these dates:


                            [2017-04-17, 2018-04-17, 2019-04-17, 2020-04-17, 2021-04-17, 2022-04-17, 2023-04-17, 2024-04-17, 2025-04-17, 2017-04-17, 2026-04-17]


                            datesList.stream().map(s -> s.getYear()).collect(Collectors.toSet()));




                            Final Set: [2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026]


                            (no dups.)


                            This code is easily callable from FMP.


                            Just another option.

                            1 of 2 people found this helpful
                            • 11. Re: Printing the range of two dates

                              The code I posted directly above, assumed you had all the dates already gathered, which was not your requirement from your original posting. So, the code I posted directly above would get the years from that already-gathered date list, but it would not create the date list itself as my script example did. So, this posting is only to clarify (and hopefully not confuse).


                              I would still opt for code readability whether imperative (as with an FMP script) or declarative (as with SQL), however. Someday, you'll need to maintain that code.   IMHO, FMP calculations are OK if short and easily readable. Since there's no debugging mechanism for FMP calculations, that's another reason I avoid CFs most of the time.



                              • 12. Re: Printing the range of two dates

                                Hey It worked but i have question, what if i want my years in this format YYYY  YYYY  YYYY, instead of YYYY,YYYY,YYYY?




                                • 13. Re: Printing the range of two dates

                                  Did you try to remove the comma in the last part of the function?  As far as I can see, that's the only comma there.


                                  • 14. Re: Printing the range of two dates

                                    Sorry I got it now, I tried removing but instead I removed it with quotes. Didnt see there were two double quotes. I got it now.