Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# IF function across multiple date fields

Topic Labels: Formulas
Solved
399 1
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

Hi All,

Hoping someone can figure out what I’m doing wrong here.

I’m trying to get the number of weeks (preferably with one decimal point) by subtracting two dates from each other. In addition, there is an IF function, as described below:
IF {date 1} < TODAY, then do the difference between {date1} & {date 2} in weeks format. If not, then do difference between TODAY() & date 2.

I have this, but it’s not working:
IF({Contract Signed Date}<TODAY(), DATETIME_DIFF({date 1},{date 2},‘days’), DATETIME_DIFF(TODAY(),{date 2},‘days’)/7

Thanks,
Dave

1 Solution

Accepted Solutions  Airtable Employee

Here’s an adjusted formula you can use. The two main differences from your formula are:

• I used `NOW()` instead of `TODAY()`, since that will give you a more accurate timestamp (since `TODAY()` defaults to 12:00am).

• I placed the / 7 outside of your nested IF statement

Here’s the formula and result:

``````IF(
{Contract Signed Date} < NOW(),
DATETIME_DIFF(
{date 1}, {date 2},' days'
),
DATETIME_DIFF(
{date 2}, NOW(), 'days'
)
)

/ 7
`````` Let me know if you have any follow up questions about the formula!  Airtable Employee

Here’s an adjusted formula you can use. The two main differences from your formula are:

• I used `NOW()` instead of `TODAY()`, since that will give you a more accurate timestamp (since `TODAY()` defaults to 12:00am).

• I placed the / 7 outside of your nested IF statement

Here’s the formula and result:

``````IF(
{Contract Signed Date} < NOW(),
DATETIME_DIFF(
{date 1}, {date 2},' days'
),
DATETIME_DIFF(
{date 2}, NOW(), 'days'
)
)

/ 7
`````` Let me know if you have any follow up questions about the formula! 