Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Nested IF statement for start date

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
714 2
cancel
Showing results for 
Search instead for 
Did you mean: 
joneill
4 - Data Explorer
4 - Data Explorer

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')))
 
 
1 Solution

Accepted Solutions
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

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!  

See Solution in Thread

2 Replies 2
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

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.