Help

Formula to pick one of two predetermined dates

Topic Labels: Dates & Timezones Formulas
367 1
cancel
Showing results for 
Search instead for 
Did you mean: 
IJMBM
4 - Data Explorer
4 - Data Explorer

Hi all. I'm trying to create a formula field that will calculate if the date should be the 1st or the 15th of the month based on another date and time field that can be any day of the year.

If the date field is any date between and including the 1st and 14th of the month, the formula needs to show the 1st of the previous month.

If the date field is any date between and including the 15th and 31st of the month, the formula needs to show the 15th of the previous month.

For example, if the date is July 13th, the formula should show June 1st, but if it's October 17th, it should show September 15th.

Any ideas on how to accomplish this would be much appreciated. Thanks.

1 Reply 1
pressGO_design
10 - Mercury
10 - Mercury

Try this:

IF(DAY(Date)<15, DATETIME_FORMAT(DATEADD(Date, -1, 'month'), 'MM/01/YYYY'), DATETIME_FORMAT(DATEADD(Date, -1, 'month'), 'MM/15/YYYY'))
 
DATETIME_PARSE(IF(DAY(Date)<15, DATETIME_FORMAT(DATEADD(Date, -1, 'month'), 'MM/01/YYYY'), DATETIME_FORMAT(DATEADD(Date, -1, 'month'), 'MM/15/YYYY')))