Convert Excel Numeric to Date

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.

EDIT: Relevant XKCD

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 numbers back to Date object in R

You can use openxlsx package to convert number to date like

library(openxlsx)

convertToDate("44025")

Or to convert the whole column you can use

convertToDate(df$date)

Reformat Excel numeric date to R date

In excel, number 40182 gives the date 04-01-2010 when formated as date.
So I think you are looking for this:

format(as.Date(40182, origin = "1899-12-30"), '%b-%Y')

which gives:

[1] "Jan-2010"

hope it helps!!!

Convert Excel numeric dates to R dates, but in some rows there is only the year given, e.g. 2018 instead of 43465

Building off of @Ronak's answer, you can use regex to determine a four digit numerical number, then pad with four trailing zeros.

x <- c(NA,NA,43465,43465,43465,43465,2018,NA,43465,43465, 43465, 43465)
ifelse(grepl('^\\d{4}$', x, perl = TRUE), 
   as.integer(paste0(x, '0000')), 
   as.integer(format(as.Date(x, origin='1899-12-30'), '%Y%m%d')))
[1]  NA       NA 20181231 20181231 20181231 20181231 20180000       NA 20181231 20181231 20181231 20181231

You'll get some warning messages regarding the NAs, and if it bothers you you can add an additional ifelse to control the NAs. Here we use a logical grep test to see if there are only four numbers (a year), then we create an integer of the values. This allows you to still use mathematical operators such as >,<,==, etc and preserve all the information.

You can change the '0000' during the paste0() call to a more appropriate number based upon the data or use case.

Convert date from excel in number format to date format python

from datetime import datetime
excel_date = 42139
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2)
tt = dt.timetuple()
print(dt)
print(tt)

As mentioned by J.F. Sebastian, this answer only works for any date after 1900/03/01

EDIT: (in answer to @R.K)

If your excel_date is a float number, use this code:

from datetime import datetime

def floatHourToTime(fh):
    hours, hourSeconds = divmod(fh, 1)
    minutes, seconds = divmod(hourSeconds * 60, 1)
    return (
        int(hours),
        int(minutes),
        int(seconds * 60),
    )

excel_date = 42139.23213
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(excel_date) - 2)
hour, minute, second = floatHourToTime(excel_date % 1)
dt = dt.replace(hour=hour, minute=minute, second=second)

print(dt)
assert str(dt) == "2015-05-15 00:13:55"

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.

---

Related Topics