IF function across multiple date fields

Topic Labels: Formulas
Solved
749 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!