# Combining an IF formula with a DATETIME_DIFF formula

Topic Labels: Formulas
347 2
cancel
Showing results for
Did you mean:
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 Date End Date Duration (what I get) Duration (what I want, instead) 1/1/2024 1/1/2024 0 1 1/1/2024 1/2/2024 1 1 1/2/2024 1/4/2024 2 2 1/4/2024 1/4/2024 0 1 1/5/2024 1/10/2024 5 5

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 Date End Date Duration 1/1/2024 1/1/2024 1 1/1/2024 1/2/2024 0 1/2/2024 1/4/2024 0 1/4/2024 1/4/2024 1 1/5/2024 1/10/2024 0

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

2 Replies 2
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')
)

12 - Earth

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