1 Reply Latest reply: May 15, 2014 9:57 AM by Gareth Horton

# Tip: Calculating Date Serial Numbers

We are running some auditing reports for our ESOP and our reporting company sends us reports with SSN in this format XXX-XX-####. This made it very difficult to compare the correct employees.  I suggested we add those last 4 numbers and their birthdate as a comparison field.  When this is done in Excel, a Birthdate of June 9, 1968 translates to 24998[/b] as a date serial number.

Excel bases their dates on Jan 1, 1990.

So I came up with this formula to calculate the Serial Number in Monarch:

[font="courier"][MyDate] - {Jan 1 1900} + 1[/font][/quote]However, this gave a date serial of 24997[/b] a difference of one day.

After I did some [url="http://www.codeproject.com/datetime/exceldmy.asp"]research[/url] I found that Excel calculates 2/29/1900 as a leap year. A bug held over from Lotus 1-2-3. The Excel bug wasn't discovered until after millions of Excel users had been using "incorrect" dates. Rather than have the users correct their spreadsheets, [url="http://support.microsoft.com/kb/214326"]MS decided[/url] to leave the error and it continues to this day!

So the alternative formula is:

[font="courier"][MyDate] - {Jan 1 1900} + If( &lt;= {Feb 28 1900}, 1, 2) /Use +2 because Excel Treats 1900 as a leap year and thus days after Feb 28 1900 are off by one day/[/font][/quote]I hope this helps some people when they have to do some date conversions and are left scratching their heads as to why Excel is off by one day.

• ###### Tip: Calculating Date Serial Numbers

Nick,

See the DateSerial1900 and DateSerial 1904 User Defined Functions in Monarch 9.

Note that the Date Serial in Excel does depend on whether you have the 1904 Date option set in Excel.

Gareth

Originally posted by Nick Osdale-Popa:

We are running some auditing reports for our ESOP and our reporting company sends us reports with SSN in this format XXX-XX-####. This made it very difficult to compare the correct employees.  I suggested we add those last 4 numbers and their birthdate as a comparison field.  When this is done in Excel, a Birthdate of June 9, 1968 translates to 24998[/b] as a date serial number.

Excel bases their dates on Jan 1, 1990.

So I came up with this formula to calculate the Serial Number in Monarch:

[font="courier"][MyDate] - {Jan 1 1900} + 1[/font][/quote]However, this gave a date serial of 24997[/b] a difference of one day.

After I did some [url="http://www.codeproject.com/datetime/exceldmy.asp"]research[/url] I found that Excel calculates 2/29/1900 as a leap year. A bug held over from Lotus 1-2-3. The Excel bug wasn't discovered until after millions of Excel users had been using "incorrect" dates. Rather than have the users correct their spreadsheets, [url="http://support.microsoft.com/kb/214326"]MS decided[/url] to leave the error and it continues to this day!

So the alternative formula is:

[font="courier"][MyDate] - {Jan 1 1900} + If( &lt;= {Feb 28 1900}, 1, 2) /Use +2 because Excel Treats 1900 as a leap year and thus days after Feb 28 1900 are off by one day/[/font][/quote]I hope this helps some people when they have to do some date conversions and are left scratching their heads as to why Excel is off by one day. /b[/quote]