Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Combining an IF formula with a DATETIME_DIFF formula

Topic Labels: Formulas
899 2
cancel
Showing results for 
Search instead for 
Did you mean: 
diane
4 - Data Explorer
4 - Data Explorer

I am trying to combine an IF formula with a DATETIME_DIFF formula and I am not getting the results expected. 

Goal: Determine the difference between two dates to see how long something takes, but never have a value of "zero." I want the lowest value to always be "one."

Start DateEnd DateDuration (what I get)Duration (what I want, instead)
1/1/20241/1/20240
1/1/20241/2/202411
1/2/20241/4/202422
1/4/20241/4/202401
1/5/20241/10/202455

 

I started with this DATETIME_DIFF formula to calculate duration:

DATETIME_DIFF(
{End Date},
{Start Date},
'days')

Then I tried introducing an IF formula to replace "zero" with "one" when duration = "zero" (I tried many variations on this):

IF(DATETIME_DIFF(
{End Date},
{Start Date},
'days'),
0,
1,
DATETIME_DIFF(
{End Date}
{Start Date},
'days'))

Airtable accepted some variations on this formula, but when it was accepted, it was automatically changed to this:

IF(DATETIME_DIFF(
{Date Finished},
{Date Started},
'days'),
0,
1)

And the result is this:

Start DateEnd DateDuration
1/1/20241/1/20241
1/1/20241/2/20240
1/2/20241/4/20240
1/4/20241/4/20241
1/5/20241/10/20240

What am I doing wrong? Any guidance would be appreciated. Thank you!

2 Replies 2
joshsorenson
6 - Interface Innovator
6 - Interface Innovator

If I'm understanding correctly then this is what you want. 

IF(
DATETIME_DIFF({End Date}, {Start Date}, 'days') = 0,
1,
DATETIME_DIFF({End Date}, {Start Date}, 'days')
)

Hi,
You forget '=0' in your formula. I think you should change comma before zero to equals sign. Even ' )=' because you need to close brackets of the first datediff.
If the date format without time, you can use following trick:

DATETIME_DIFF({End Date},{Start Date},'days') + ({End Date}={Start Date})