9 Replies Latest reply on Sep 1, 2011 1:35 PM by rebby_575

    Find first and last day of month

    deliciousapple

      Title

      Find first and last day of month

      Post

      I have two date fields, "begin date" and "end date".  I would like a calculation that finds the first day of the month and the last day of the month.  Once I have this I will write a script to set the fields with calc. 

      Any ideas?

        • 1. Re: Find first and last day of month
          sunmoonstar.13

          Set the "Begin date" field with this calculation:

          Date ( Month ( Date ) ; 1 ; Year ( Date ) )

           

          Set the "End date" field with this calculation:

          Case (
          Month ( Date ) + 1 > 12 ;
          Date ( 1 ; 1 ; Year ( Date ) +1 ) - 1 ;
          Date ( Month ( Date ) + 1 ; 1 ; Year ( Date ) ) - 1
          )

           
          Substitute Date with your record creation date field (which I presume you already have, or if you don't, you should have).

           

           Nick

           

          • 2. Re: Find first and last day of month
            LaRetta_1

            You can also use:


            Date ( Month ( date ) + 1 ; 0 ; Year ( date ) )

            • 3. Re: Find first and last day of month
              sunmoonstar.13

              > Date ( Month ( date ) + 1 ; 0 ; Year ( date ) )

              Nice! Makes the calc look tidier, too:

              Case (
              Month ( Date ) + 1 > 12 ;
              Date ( 1 ; 0 ; Year ( Date ) +1 ) ;
              Date ( Month ( Date ) + 1 ; 0 ; Year ( Date ) )
              )


              Nick

              • 4. Re: Find first and last day of month
                deliciousapple

                Hey thank you, works like a charm!!!

                I would like to understand how this works.  would you be so kind as to write the given result for each step of the function?

                If not no problem, you guys have already been a great help as the function works great!!

                Thanks!

                • 5. Re: Find first and last day of month
                  LaRetta_1

                  I think DeliciousApple is speaking to you, Nick. :^)

                  • 6. Re: Find first and last day of month
                    deliciousapple

                    Oh I'm not that picky, you can jump in there LaRetta!

                    • 7. Re: Find first and last day of month
                      LaRetta_1

                      Well, I'm unsure which calculation you want explained.  If you want Nick's calc then he should explain it ...

                      The one I gave does it all - no need for the Case() calc at all:

                      Date ( Month ( date ) + 1 ; 0 ; Year ( date ) )

                      There isn't much to explain.  It jumps the date forward into the next month but then says it wants day 0.  Day zero drops the date back one day into the prior month (and the last day of the prior month).

                      With FileMaker dates, you can increment the month ahead even if it is greater than a valid month.  So you could have:

                      Date ( Month ( date ) + 200 ... and it would properly increment forward by that number of months just as you can have the day portion as Day ( date ) + 900 and it would properly increment forward (and backward) even if there aren't that many days in that month. 

                      I guess I didn't explain that I wasn't suggesting Nick replace PART of his calc with mine but rather replace ALL of his calc with mine.

                      • 8. Re: Find first and last day of month
                        sunmoonstar.13

                        LaRetta is quite right, you can replace my entire Case calc with Date ( Month ( Date ) + 1 ; 0 ; Year ( Date ) ).

                        My original calc worked out the last day of the month by working out the first day of the following month and then subtracting one day. The Case part of it came into play if the month was December, because it then had to work out the value of the following year as well and then subtract one day to get 31 December of the current year. With the zero in the calc above, you don't have to worry about any of that. The zero effectively "subtracts" one day from the first day of the next month, no matter what month it is.

                        Nick

                        • 9. Re: Find first and last day of month
                          rebby_575

                          Old thread, but I just found it looking for this very thing, and then found that with Filemaker 11  you can use wildcards (asterisks) in dates.

                          The calcs above are replaced with:

                           month(your_date) & "/*/" & year(your_date) 

                          So cool.