Help

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
Jump to Solution
399 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Hi @David_Gutierrez,

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

Screen Shot 2020-07-14 at 4.00.54 PM

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

See Solution in Thread

1 Reply 1

Hi @David_Gutierrez,

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

Screen Shot 2020-07-14 at 4.00.54 PM

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