Date Arithmetic in Spreadsheets

Question

I have a bunch of data in a spreadsheet where the left-most column represents a date in the format DD-MM. Here's an example for column A:

15-09

23-09

25-09

30-09

02-10

I took that date and in, LibreOffice Calc, created another column to add the year and make it DD-MM-YYYY format in column B (the data span multiple years).

=CONCATENATE(A1,"-2011")

Then I formatted the cells in column B as Date, with the appropriate (user-defined) DD-MM-YYYY format. Calc seemed to recognize it as a date and I thought all was good. But when I tried to do a "days between" calculation, e.g.

=B2-B1

all I got me was "#VALUE!". Nothing I did to change the format seemed to make the calculation work. I tried the same in Google Spreadsheets and got the same result.

How can I take date-formatted data then do a calculation on it?

Answer 1

You need to wrap the formula where you catenate the year with:

DATEVALUE(....)

Which will turn the text date into a date value that you can then use in date calculations.

Note however, that on my UK based system, using dd-mm-yyyy as a date format will fail (Err:502).

Using yyyy-mm-dd should always be safe as this is the ISO date format and should work worldwide. Using region specific date formats is always fraught with danger and should be avoided if at all possible.