Help

IF function across multiple date fields

Topic Labels: Formulas
Solved
Jump to Solution
749 1
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Gutierre1
5 - Automation Enthusiast
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
Jason
Airtable Employee
Airtable Employee

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
Jason
Airtable Employee
Airtable Employee

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!