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
B U
none

Share on Facebook Share on Twitter

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

RoundDate ( "3/14/2017" )

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

3/1/2017

3/1/2017

  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.

 

Comments

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 BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.