Excel dates do not match SQL Server dates

Overview

There is a bug in excel that number representations for dates (ie 1 = 01/01/1900) do not match up with SQL Server number representations for dates. Apparently this is something that is known but will not be fixed as it will break to many peoples spreadsheets!

How dates work in Excel

Dates in excel are really just numbers formatted as a date. This picture shows the first column as the number representation of the date. All i have done is to copy the column across to B and reformat it to a Date column.

The Bug

This shows there is a 29 Feb 1900 when it never existed! So it adds another date to the calender, thereby ruining any sort of consistency with the number representation of the date.

In SQL Server

Dates work the same way in SQL Server except they start from 0 rather than 1.

DateID      SQL Dates

----------- -----------------------
0           1900-01-01 00:00:00.000
1           1900-01-02 00:00:00.000
2           1900-01-03 00:00:00.000
3           1900-01-04 00:00:00.000

And in SQL Server the dates are correct...

SELECT 58 AS DateID, CONVERT(DATETIME,58) AS [SQL Dates] UNION ALL
SELECT 59, CONVERT(DATETIME,59) UNION ALL
SELECT 60, CONVERT(DATETIME,60) UNION ALL
SELECT 61, CONVERT(DATETIME,61)

Returns the following

DateID      SQL Dates
----------- -----------------------
58          1900-02-28 00:00:00.000
59          1900-03-01 00:00:00.000
60          1900-03-02 00:00:00.000
61          1900-03-03 00:00:00.000

So what you get in the end that it can be complicated to link the dates to Excel if you are trying to match up Excel to SQL Server with the number representation of the date