Serial Values for Dates (2000 SP3)

Topic

New Reply

I was wondering if there was a function or some way to convert a date to a serial value like in excel.
example
1/1/1900 = 1
4/1/2006 = 38808
4/2/2006 = 38809

Thanks,

Reply | Quote

Replies

Use the CLng function.
In a query: CLng([DateField])
In the control source of a text box: =CLng([DateField])
(1/1/1900 corresponds to 2, by the way. Excel contains a deliberate error in dates before March 1, 1900)

Reply | Quote

Thank You and it is good to know that excel contains that deliberate error.

Reply | Quote

The error in Excel was introduced for compatibility with Lotus 1-2-3, which was the market leader in spreadsheets in the 1980’s. The 1-2-3 programmers hadn’t taken into account that 1900 was not a leap year, so dates before March 1, 1900 are off by 1. In order to win over Lotus customers, the Excel programmers wanted Excel to be able to open and edit Lotus 1-2-3 spreadsheets without conversion, so they did the same. VBA, however, uses the “official” calendar rules.

Reply | Quote