When you are working with cells formatted as having dates, if you subtract them, you get how many days separate those dates.

Problems might arise when you are writing a “date” on some cell, but you are using a custom format for the year, month and day.
Because of the custom format, functions like “DATEVALUE”, that return the date’s serial number, might not work properly.

Here is one simple situation I faced today:

(1) I have cells with a custom date format: yyyy/mm/dd
for example, 2009/01/11 respects that format.

(2) I would like to now how many days have gone by since, say, 2009/01/01, relatively to the content of some cell.

This expression answers that question:
Date(2009,1,11) – Date(2009,1,1) = 10

However, the problem is that Date was not performing correctly with some of my cells, so expressions like Date(referenceToSomeCell) could fail.

(3) My solution was to transform each of the cells with “dates” to text, stating their date format:

TEXT (referenceToSomeCell, “yyyy/mm/dd”)

This “yyyy/mm/dd” format states 4 digits for the year, then a forward slash, then 2 digits for the month, then a forward slash, followed by 2 digits for the day.

Having done this, it is easy to extract the corresponding date’s “serial number”, which is the value that will allow basic math, like computing differences.
The function to use is “DATEVALUE”, that expects a “date string”.

DATEVALUE (TEXT(referenceToSomeCell, “yyyy/mm/dd”))

(4) So, the straight to the point formula to my original problem is:

DATEVALUE (TEXT(referenceToCell, “yyyy/mm/dd”)) – DATE (2009, 1, 1)

Example:
– imagine cell A9 holds 2009/01/11
– the number of days in 2009, up to then, can be computed by the EXCEL expression
DATEVALUE(TEXT(A9, “yyyy/mm/dd”)) – DATE (2009, 1, 1)
– the answer is 10
– if we wanted the answer to be inclusive, ie include day 11 also, the expression would be
DATEVALUE(TEXT(A9, “yyyy/mm/dd”)) – DATE (2009, 1, 1) +1

Tags: , ,