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.
This question is related to
excel
date
number-formatting
The answer is
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
If your date is text and you need to convert it then DATEVALUE will do this:
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 < DATE('1900-03-01')
THEN 1
ELSE 0
END
Source: Stackoverflow.com