6 Replies Latest reply on Aug 21, 2014 4:15 PM by robwoof

    Custom Function for Date Range with Leading Zeros

    fmdataweb

      I've been working with a Custom Function that generates a list of dates based on 2 input dates (start date and end date) as follows:

       

      If(

      GetAsDate ( EndDate ) > GetAsDate ( StartDate );

      GetAsDate ( StartDate ) & ¶ &

      DateRange (GetAsDate ( StartDate ) + 1; EndDate );

      GetAsDate ( StartDate )

      )

       

      The list of dates that is returned (in DD/MM/YYYY) format might look as follows:

       

      29/09/2014

      30/09/2014

      1/10/2014

      2/10/2014

      3/10/2014

       

      I need to have the list always show the dates with leading zeros for the day and month, i.e.:

       

      29/09/2014

      30/09/2014

      01/10/2014

      02/10/2014

      03/10/2014

       

      I haven't been able to work this out so far - can anybody help with the required modification to have the date range in DD/MM/YYYY with leading zeros for the DD and MM?

       

      thanks,

      Steve

        • 1. Re: Custom Function for Date Range with Leading Zeros
          robwoof

          Steve,

           

          You will need to make the Custom Function a bit more involved, alas. Something like:

           

          Let ( [ start = GetAsDate ( StartDate ) ;

                    end = GetAsDate ( EndDate ) ;

                    startDay = Right ( "0" & Day ( start ) ; 2 ) ;

                    startMonth = Right ( "0" & Month ( start ) ; 2 ) ;

                    startYear = Year ( start ) ;

                    startText = Substitute ( List ( startDay ; startMonth ; startYear ) ; "¶" ; "/" ) ] ;

           

          If ( end > start ;

                       List ( startText ; DateRange ( start + 1 ; end ) ;

                       startText )

           

          )

           

          HTH

          Rob

          • 2. Re: Custom Function for Date Range with Leading Zeros
            fmdataweb

            Hi Rob,

             

            I'm still getting a list of dates without the leading zeros using your suggested calculation, e.g. using the start date of 26/09/2014 and end date of 10/10/2014 it's returning:

             

            26/09/2014

            27/09/2014

            28/09/2014

            29/09/2014

            30/09/2014

            1/10/2014

            2/10/2014

            3/10/2014

            4/10/2014

            5/10/2014

            6/10/2014

            7/10/2014

            8/10/2014

            9/10/2014

            10/10/2014

             

            Any other takers?

            • 3. Re: Custom Function for Date Range with Leading Zeros
              ch0c0halic

              Modified Custom function:

              (Don't copy the lines with stars).

               

                      • Start Function *****

              Let ( [

                        start = GetAsDate ( StartDate ) ;

               

                        end = GetAsDate ( EndDate ) ;

               

                        startDay = Right ( "0" & Day ( start ) ; 2 ) ;

               

                        startMonth = Right ( "0" & Month ( start ) ; 2 ) ;

               

                        startYear = Year ( start ) ;

               

                        startText = Substitute ( List ( startDay ; startMonth ; startYear ) ; "¶" ; "/" ) ] ;

               

               

               

              If ( end > start - 1 ;

               

                           List ( startText  ; DateRange ( start + 1 ; end ) ;

               

                            )

               

               

               

              )

              )

                      • End Function *****

               

              I tested the function in the data viewer using this calc:

               

              Let ( [

              StartDate = "9/28/14" ;

              EndDate = "10/10/14"  ] ;

               

              DateRange ( StartDate ; EndDate )

              )

               

              Results:

              28/09/2014

              29/09/2014

              30/09/2014

              01/10/2014

              02/10/2014

              03/10/2014

              04/10/2014

              05/10/2014

              06/10/2014

              07/10/2014

              08/10/2014

              09/10/2014

              10/10/2014

              • 4. Re: Custom Function for Date Range with Leading Zeros
                beverly

                Love this "right()", but as I can also deal with "vague" dates, the day and/or month can sometimes be "0", so I use:

                 

                     Right ( "00" & Day ( date ) ; 2 )

                 

                and

                 

                     Right ( "00" & Month ( date ) ; 2 )

                 

                While not "proper dates" in FileMaker (and indeed these will always be converted), they are valid when "date-as-text" for things like genealogy or other dates that may be somewhat vague.

                 

                So, it does not hurt to go ahead and make the "00" instead of "0" every time, as I prefer...

                 

                 

                 

                Beverly

                • 5. Re: Custom Function for Date Range with Leading Zeros
                  fmdataweb

                  Thank you very much - it's working well so far in my tests. Much appreciated.

                  • 6. Re: Custom Function for Date Range with Leading Zeros
                    robwoof

                    Bizarre. When I tried my own code, it gave an error because I missed a ')' at the end of the List function, but using the Data Viewer with

                     

                    DateRange ( Date ( 4 ; 30 ; 2014 ) ; Date ( 5 ; 5 ; 2014 ) )

                     

                    ( once I corrected the missing ')' ) gave

                     

                    30/04/2014

                    01/05/2014

                    02/05/2014

                    03/05/2014

                    04/05/2014

                    05/05/2014

                     

                    which is what you wanted.