User:Eurodog/sandbox300

---

Excel date serial number

Basis for date serial numbers

Date serial numbers, in Excel, start from January 1, 1900, with the serial number 1. The serial number for today's date – Saturday, November 9, 2024 – is 45605 .

Note: Excel dates after 28th February 1900 are actually one day out. Excel behaves as though the date 29th February 1900 existed, which it didn't.

Microsoft intentionally included this bug in Excel so that it would remain compatible with the spreadsheet program that had the majority market share at the time; Lotus 1-2-3.

Lotus 1-2-3 was incorrectly programmed as though 1900 was a leap year. This isn't a problem as long as all your dates are later than 1st March 1900

Time serial numbers

Time serial numbers are calculated by multiplying three fractions:

1. The hour fraction, expressed as a fraction of the day (the hour as numerator, and 24 – representing the number of hours in a day – as denominator), plus
2. The minute fraction, expressed as a fraction of the hours (the minute as numerator, and 1440 – representing the number of minutes in a day – as denominator), plus
3. The second fraction, expressed as a fraction of the minute (the second as numerator, and 86,400 – representing the number of seconds in a day – as denominator).

Getting the serial number from a date format

Excel determines whether a number value is actually a date value.

Problems with different country customs

A date formatted as a text can be converted to a date value. And, regardless of how a date value is formatted (M/D/Y or D/M/Y or Y/M/D, etc.), the text to date value will be correct if the country settings in Excel match the recognized date value).

However, in Germany, Excel and database users typically format the date as DDMMYYYY. And, of course, any order can be formatted in Excel (in a number of ways, namely in the format number settings or using TEXT format settings in a function). If the DD value is 12 or less, an Excel user in the U.S. may read it was the month.

One solution, in theory, would be to figure out a way to always read a file from Germany as Text, then simply convert the text to date values using mid, left, right parsing functions. The problems, however, is that Excel won't let users read DDMMYYYY as text if it is already a date value. And, in cases users have uploaded database files and converted text-to-columns, sometimes the date values end-up mixed. The one with DD values equal to or less than 12 might be read as an MM value.

NEVER design an if/then function to determine whether a German date is a value.