DAYS versus DATEDIF

[lodf2023]

In the topic below, the user asked the question how to calculate number of days between two dates and how to handle blanks. The solution was to use days.

Just asking the users of this forum, any preference between days versus datedif?

Also, I guess I never ran into this until recently, isblank isn’t always the solution, specifically if the cell contains a formula. Searching for a consistent, flexible, long-term solution, I found the functions below. I prefer isblank for its’ clarity. But if the spreadsheet starts out with hardcodied values, but later become functions as it develops, this could create problems that are not obvious at first. Thoughts?

=len(trim(c1))=0 

=AND(c1 <> "",IF(ISERROR(VALUE(LEFT(c1))),0,1))

---

Topic:

Formula to calculate number of Days between two cells incase of one of them is blank or have a Date

Assuming the dates are not text, something along the lines of =DAYS(B1,IF(ISBLANK(C1),TODAY(),C1))

[robleyd]

@lodf2023

any preference between days versus datedif

Depends on what you want to return; if you look at the links you provided, DAYS() only returns days (and may return a fraction of a day when the arguments include time parts) whereas DATEDIF only returns whole units of the chosen interval.

[Lupp]

In addition:

• DATEDIF() requires that its first argument is less than or equal to the second one.
  It returns an error otherwise.
• The abbreviation DATEDIF is absurd. For dyslexic people it may make no diferense.
• =DAYS(DateTime1 ; DaterTime2) formats the result as an ordinary number while the arithmetically eqivalent =-(DateTime1-DateTime2) sets an automatic Format.
  Depending on the numberformats of the arguments this may be [HH]:MM:SS . (Bad/misleading in this case.)

@karolus
Once in a while there must be allowed a little joke.

[lodf2023]

Definitely going to lean towards using days(), months(), years() rather than datedif() where interval can be “d”, “m” or “y”.