Brian Dunning's FileMaker Custom Functions

RoundDate ( TheDate )

Rounds the date to the closest first day of the month. When the day is smaller or equal to 15 it goes to the first day of TheDate. If it's bigger than 15 then it goes to the next month's first day.

  Average rating: 4.7 (26 votes) Log in to vote

karl J   karl J

Share on Facebook Share on Twitter

  Sample input:
RoundDate ( "3/28/2017" )

RoundDate ( "3/14/2017" )

RoundDate ( "3/15/2017" )
  Sample output:



  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

If the date is is smaller or equal to 15 then use the same month's first day.
ex: RoundDate ( "3/14/2017" ) --> 3/1/2017

If the date is bigger than 15 then use the next month's first day of the month.
ex: RoundDate ( "3/28/2017" ) --> 4/1/2017

If the TheDate's month is 12 and TheDate's month is bigger than 15 then go to next year's 1/1/Year (TheDate) +1
ex: RoundDate ( "12/28/2017" ) --> 1/1/2018

IsValid () Does check TheDate if it's a valid date. Gives a blank result if TheDate is not a valid date. Does also check for correct last day of the month and February's correct last day of the month.

Would love some corrections. Purposely did not use Case () in order to reduce calculations as much as possible to keep the function as efficient as possible according to my knowledge.



Daniele Raybaudi   Daniele Raybaudi, ACI
Apr 11, 2013
What about:

If ( IsValid ( GetAsDate ( TheDate ) ) ; Date ( Month ( TheDate ) + ( Day ( TheDate ) > 15 ) ; 1 ; Year ( TheDate ) ) )
Karl J   Karl J, Location Unknown
Apr 11, 2013
Thank you Daniele, I didn't think about using a condition that way in my functions. Thank you!
I put credits for you.

Log in to post comments.


Note: these functions are not guaranteed or supported by Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 16 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: