Help to calculate days between dates but show 0 if dates are the same

Question

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 😊

proboticsolutions · Accepted Answer

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! 😊

Caitlyn10 · Answer

Hi ​@proboticsolutions, thank you so much!! That worked perfectly👌🏻Really appreciate the help.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded