Formula to convert date to number

excel date number-formatting
user2401464 picture user2401464 · Nov 1, 2013 · Viewed 158.2k times · Source

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.

Answer

Coyne Tibbets picture Coyne Tibbets · Sep 29, 2015

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

Related questions

Count number of times a date occurs and make a graph out of it

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number …

Read More
Ordering issue with date values when creating pivot tables

I have a 2-column spreadsheet which contains Dates and Sales figures as follows: When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to …

Read More
Excel SUMIF between dates

I have column A with date values formatted as mm/dd/yyyy. I am trying to sum the values of column B if A >=DATE(2012,1,1) AND =SUM(B:B) sums B properly, but if I try to use =SUMIF(…

Read More