Skip to main content

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/202401 
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!

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})

 

Reply


Terms & Conditions