Formula to convert date to number

Question

I would like to know the formula to convert a date in 10 26 2013 to 41573 number as done in Excel   Like how 10 26 2013 is converted to 41573

User · Accepted Answer

If you change the format of the cells to General then this will show the date value of a cell as behind the scenes Excel saves a date as the number of days since 01/01/1900

Screenprint 1

Screenprint 2

If your date is text and you need to convert it then DATEVALUE will do this:

Datevalue function

User · Answer

The Excel number for a modern date is most easily calculated as the number of days since 12 30 1899 on the Gregorian calendar   Excel treats the mythical date 01 00 1900  i e   12 31 1899  as corresponding to 0  and incorrectly treats year 1900 as a leap year  So for dates before 03 01 1900  the Excel number is effectively the number of days after 12 31 1899    However  Excel will not format any number below 0  -1 gives you             and so this only matters for  01 00 1900  to 02 28 1900  making it easier to just use the 12 30 1899 date as a base   A complete function in DB2 SQL that accounts for the leap year 1900 error   SELECT    DAYS INPUT DATE                      - DAYS DATE  1899-12-30       - CASE                                WHEN INPUT DATE  lt  DATE  1900-03-01                THEN 1                           ELSE 0                     END

[excel] Formula to convert date to number

Examples related to excel

Examples related to date

Examples related to number-formatting