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

# Find first and last day of month

### 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

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

You can also use:

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

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

> 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

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

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

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

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

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

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

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

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.