Real Dates and Times

Sometimes spreadsheets with imported data (or data that was entered incorrectly) end up with dates and times being seen as text and not real numbers. You can spot this easily in Excel by widening the columns a bit, selecting a column, right-clicking and selecting Format → Cells → Alignment, and then changing the Horizontal alignment to General (the default format for cells). Click OK and examine your dates and times closely. If any are not rightaligned, Excel doesn't think they are dates.

To fix this, first copy any empty cell, and then select the column and format as any Date and/or Time format. While the column is still selected, right- click and select Paste Special → Value → Add. This will force Excel to convert any text dates and times to real dates and times. You might need to change the format again. Another simple method is to reference the cell(s) like this:

=A1+0 or A1*1