Skip to main content

Dear All,

Im trying to create a formula where if an someone starts before the 15th of the month then return the 1st of that month. If they start on the 15th or after then return the 1st of the following month.

I have created the below nested IF statement but only the first IF statement appears to be working. For start date equal to or greater than the 15th it is giving me the 1st of the month and not the first of the next month.

Please advise 

 
IF(
DATETIME_DIFF({Role Start Date},'days'<15),
DATEADD(DATETIME_FORMAT({Role Start Date},'YYYY-MM'),0,'months'),
IF(
DATETIME_DIFF({Role Start Date},'days'>=15),
DATEADD(DATETIME_FORMAT({Role Start Date},'YYYY-MM'),1,'months')))
 
 

Welcome @joneill!

The datetime_diff formula substracts between two date times, so the format you are using it in will not evaluate the way you are looking for in the equation. 

Try something like this-

IF(
  DATETIME_FORMAT({Role Start Date},'D')<15,
  DATETIME_FORMAT({Role Start Date}, 'YYYY-MM'),
  DATETIME_FORMAT(DATEADD({Role Start Date}, 1,'month'), 'YYYY-MM')
)

I don't think you really need the nested if statement because you are basically stating that if it is less than 15 days do this, for everything else, days greater than 15, do this other thing.

All the best!  

Welcome @joneill!

The datetime_diff formula substracts between two date times, so the format you are using it in will not evaluate the way you are looking for in the equation. 

Try something like this-

IF(
  DATETIME_FORMAT({Role Start Date},'D')<15,
  DATETIME_FORMAT({Role Start Date}, 'YYYY-MM'),
  DATETIME_FORMAT(DATEADD({Role Start Date}, 1,'month'), 'YYYY-MM')
)

I don't think you really need the nested if statement because you are basically stating that if it is less than 15 days do this, for everything else, days greater than 15, do this other thing.

All the best!  


Thank you Brian, Greatly appreciated!

I tried it without the nested IF statement prior to posting but it didn't work, possibly because of the two different date adds.

Your solution above works perfectly.

Thanks Again.

Reply


Terms & Conditions