IF function across multiple date fields

[David_Gutierre1]

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

[Jason · Accepted solution]

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

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