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
714 2
cancel
Showing results for
Did you mean:
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.

IF(
DATETIME_DIFF({Role Start Date},'days'<15),
IF(
DATETIME_DIFF({Role Start Date},'days'>=15),

1 Solution

Accepted Solutions
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!

2 Replies 2
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!

4 - Data Explorer

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.