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 B U none |
RoundDate ( "3/14/2017" )
RoundDate ( "3/15/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, ACI Apr 11, 2013 |
|
| What about: If ( IsValid ( GetAsDate ( TheDate ) ) ; Date ( Month ( TheDate ) + ( Day ( TheDate ) > 15 ) ; 1 ; Year ( TheDate ) ) ) |
||
 |
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. |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.