Skip to main content

Hi, can someone please help with my formula? I’d like to calculate the number of days between two dates, but if the dates are the same then they should display 0.

 

The formula below works to show the ones with the same date as 0, however it then shows the ones with different dates as being one figure too little.

 

DATETIME_DIFF(

  {Post Sale Date},

  {Auction Date/Time (Local)},

  'days'

)

 

To fix this I added +1 to the formula, which of course, solves the latter issue but not the issue for same dates need to show 0.

 

DATETIME_DIFF(

  {Post Sale Date},

  {Auction Date/Time (Local)},

  'days'

) +1

 

Can someone please assist 😊

Hi ​@Caitlyn10,
 

I think you can use this formula to fix the issue:
IF(
  DATETIME_FORMAT({Post Sale Date}, "YYYY-MM-DD") = DATETIME_FORMAT({Auction Date/Time (Local)}, "YYYY-MM-DD"),
  0, 
  DATETIME_DIFF({Post Sale Date}, {Auction Date/Time (Local)}, 'days')+1
)
 

This checks if the dates are the same (ignoring time) and returns 0 if they are, while calculating the difference in days for different dates.

Hope that works for you! 😊

 

Hi ​@proboticsolutions, thank you so much!! That worked perfectly 👌🏻

Really appreciate the help.

Terms & Conditions