Convert Excel Date Serial Number to Regular Date

convert Excel Date Serial Number to Regular Date

In SQL:

select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

In SSIS, see here

http://msdn.microsoft.com/en-us/library/ms141719.aspx

Converting a serial number into date

I asked a similar question a while ago - it wasn't talking about Excel but VB6, hence I'm not suggesting this question is a duplicate. However, the answer I think is the same.

Date 0 in SQL is 01/01/1900.

Date 0 in VB6 is 30/12/1899, 2 days earlier - that explains the difference.

So I believe Excel to be the same.

In Excel, I entered 0 in a cell and formatted the cell as a date - which displayed (weirdly IMO) 0/1/1900. I've had a quick look to find another reference that answers this definitively, but haven't managed to find one.

Edit:

Pulling in links that were given to me in my question (that do relate to Excel):

http://www.ozgrid.com/forum/showthread.php?t=46561

http://www.joelonsoftware.com/items/2006/06/16.html

Converting Excel Date Serial Number to Date using JavaScript in All timezone

Using the below function we can convert excel date to javascript date in all timezone.

ExcelDateToJSDate(serial) {
                 var hours = Math.floor((serial % 1) * 24);
                 var minutes = Math.floor((((serial % 1) * 24) - hours) * 60)
                 return new Date(Date.UTC(0, 0, serial, hours-17, minutes));
        }

How to convert Excel date format to proper date in R

You don't need to use lubridate for this, the base function as.Date handles this type of conversion nicely. The trick is that you have to provide the origin, which in Excel is December 30, 1899.

as.Date(42705, origin = "1899-12-30")
# [1] "2016-12-01"

If you want to preserve your column types, you can try using the read_excel function from the readxl package. That lets you load an XLS or XLSX file with the number formatting preserved.

Converting Excel Date Serial Number to Date using Javascript

Try this:

function ExcelDateToJSDate(serial) {
   var utc_days  = Math.floor(serial - 25569);
   var utc_value = utc_days * 86400;                                        
   var date_info = new Date(utc_value * 1000);

   var fractional_day = serial - Math.floor(serial) + 0.0000001;

   var total_seconds = Math.floor(86400 * fractional_day);

   var seconds = total_seconds % 60;

   total_seconds -= seconds;

   var hours = Math.floor(total_seconds / (60 * 60));
   var minutes = Math.floor(total_seconds / 60) % 60;

   return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}

Custom made for you :)

Convert Excel date format to regular date with postgres

Excel dates represent the number of days since December 30th, 1899 (well, approximatly - early years are inaccurate).

In Postgres you could do:

date '1899-12-30' + myexceldate::int * interval '1' day

Where myexceldate is the name of the text column that holds the excel number.

Converting excel DateTime serial number to R DateTime

Your number is counting days. Convert to seconds, and you're all set (less a rounding error)

helpData[["ExcelDate"]] <- 
  as.POSIXct(helpData[["ExcelNum"]] * (60*60*24)
    , origin="1899-12-30"
    , tz="GMT")


#     ID        DateTime ExcelNum           ExcelDate
#  1   1   3/4/2011 6:00 40606.25 2011-03-04 06:00:00
#  2   2  3/11/2011 7:55 40613.33 2011-03-11 07:54:59
#  3   3  3/13/2011 7:55 40615.33 2011-03-13 07:54:59
#  4   4  3/14/2011 0:00 40616.00 2011-03-14 00:00:00
#  5   5 3/14/2011 10:04 40616.42 2011-03-14 10:03:59
#  6   6  3/14/2011 7:55 40616.33 2011-03-14 07:54:59
#  7   7 3/15/2011 19:55 40617.83 2011-03-15 19:54:59
#  8   8  3/17/2011 7:55 40619.33 2011-03-17 07:54:59
#  9   9  3/18/2011 4:04 40620.17 2011-03-18 04:03:59
#  10 10  3/18/2011 4:04 40620.17 2011-03-18 04:03:59

How to convert a column with Excel Serial Dates and regular dates to a pandas datetime?

• All the dates can't be parsed in the same manner
• Load the dataframe
• Cast the dates column as a str if it's not already.
• Use Boolean Indexing to select different date types
  
  • Assuming regular dates contain a /
  • Assuming Excel serial dates do not contain a /
• Fix each dataframe separately based on its datetime type
• Concat the dataframes back together.

import pandas as pd
from datetime import datetime

# load data
df = pd.DataFrame({'dates': ['09/01/2020', '05/15/1985', '06/07/2013', '33233', '26299', '29428']})

# display(df)

        dates
0  09/01/2020
1  05/15/1985
2  06/07/2013
3       33233
4       26299
5       29428

# set the column type as a str if it isn't already
df.dates = df.dates.astype('str')

# create a date mask based on the string containing a /
date_mask = df.dates.str.contains('/')

# split the dates out for excel
df_excel = df[~date_mask].copy()

# split the regular dates out
df_reg = df[date_mask].copy()

# convert reg dates to datetime
df_reg.dates = pd.to_datetime(df_reg.dates)

# convert excel dates to datetime; the column needs to be cast as ints
df_excel.dates = pd.TimedeltaIndex(df_excel.dates.astype(int), unit='d') + datetime(1900, 1, 1)

# combine the dataframes
df = pd.concat([df_reg, df_excel])

display(df)

       dates
0 2020-09-01
1 1985-05-15
2 2013-06-07
3 1990-12-28
4 1972-01-03
5 1980-07-28

---

Related Topics