Clever Geek Handbook

Convert date from Excel to number format to python date format

I am reading data from excel and manipulating the data using python. But dates go as whole numbers. How do I convert dates to date format?

5/15/2015 goes like 42139.00

+3


source to share


2 answers 


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 J. F. Sebastian mentioned, this answer only works for any date after 1900/03/01

EDIT: (in response to @RK)



If yours excel_date

is a floating point number use this code:

def floatHourToTime(fh):
    h, r = divmod(fh, 1)
    m, r = divmod(r*60, 1)
    return (
        int(h),
        int(m),
        int(r*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)
+5


source


In xlrd, these are alaways nums, but you can use xldate_as_tuple to convert it to python date. http://www.lexicon.net/sjmachin/xlrd.html#xlrd.xldate%5Fas%5Ftuple-function



0


source






More articles:


All Articles