How do I read a date in Excel format in Python

Question

How can I convert an Excel date  in a number format  to a proper date in Python

User · Answer

Here s the bare-knuckle no-seat-belts use-at-own-risk version   import datetime  def minimalist xldate as datetime xldate  datemode         datemode  0 for 1900-based  1 for 1904-based     return           datetime datetime 1899  12  30            datetime timedelta days xldate   1462   datemode

User · Answer

A combination of peoples post gave me the date and the time for excel conversion  I did return it as a string  def xldate to datetime xldate     tempDate   datetime datetime 1900  1  1    deltaDays   datetime timedelta days int xldate     secs    int  xldate 1  86400 -60    detlaSeconds   datetime timedelta seconds secs    TheTime    tempDate   deltaDays   detlaSeconds     return TheTime strftime   Y- m- d  H  M  S

User · Answer

After testing and a few days wait for feedback  I ll svn-commit the following whole new function in xlrd s xldate module     note that it won t be available to the diehards still running Python 2 1 or 2 2        Convert an Excel number  presumed to represent a date  a datetime or a time  into   a Python datetime datetime    param xldate The Excel number    param datemode 0  1900-based  1  1904-based     lt br gt WARNING  when using this function to   interpret the contents of a workbook  you should pass in the Book datemode   attribute of that workbook  Whether   the workbook has ever been anywhere near a Macintosh is irrelevant     return a datetime datetime object  to the nearest second     lt br gt Special case  if 0 0  lt   xldate  lt  1 0  it is assumed to represent a time    a datetime time object will be returned     lt br gt Note  1904-01-01 is not regarded as a valid date in the datemode 1 system  its  serial number    is zero     throws XLDateNegative xldate  lt  0 00    throws XLDateAmbiguous The 1900 leap-year problem  datemode    0 and 1 0  lt   xldate  lt  61 0     throws XLDateTooLarge Gregorian year 10000 or later    throws XLDateBadDatemode datemode arg is neither 0 nor 1    throws XLDateError Covers the 4 specific errors  def xldate as datetime xldate  datemode       if datemode not in  0  1           raise XLDateBadDatemode datemode      if xldate    0 00          return datetime time 0  0  0      if xldate  lt  0 00          raise XLDateNegative xldate      xldays   int xldate      frac   xldate - xldays     seconds   int round frac   86400 0       assert 0  lt   seconds  lt   86400     if seconds    86400          seconds   0         xldays    1     if xldays  gt    XLDAYS TOO LARGE datemode           raise XLDateTooLarge xldate       if xldays    0            second   seconds   60  minutes   seconds    60         minutes  second   divmod seconds  60            minute   minutes   60  hour      minutes    60         hour  minute   divmod minutes  60          return datetime time hour  minute  second       if xldays  lt  61 and datemode    0          raise XLDateAmbiguous xldate       return           datetime datetime fromordinal xldays   693594   1462   datemode            datetime timedelta seconds seconds

User · Answer

This is a revised version from  hounded  My code handles both date and time  something like 43705 591795706      import math     import datetime       def xldate to datetime xldatetime    something like 43705 6158241088        tempDate   datetime datetime 1899  12  31         days  portion    math modf xldatetime         deltaDays   datetime timedelta days days         changing the variable name in the edit       secs   int 24   60   60   portion        detlaSeconds   datetime timedelta seconds secs        TheTime    tempDate   deltaDays   detlaSeconds         return TheTime strftime   Y- m- d  H  M  S     xldate to datetime 43705 6158241088    2019-08-29 14 46 47

User · Answer

excel stores dates and times as a number representing the number of days since 1900-Jan-0  if you want to get the dates in date format using python  just subtract 2 days from the days column  as shown below   Date   sheet cell 1 0  value-2   in python  at column 1 in my excel  i have my date and above command giving me date values minus 2 days  which is same as date present in my excel sheet

User · Answer

If you have a datetime column in excel file  Then below code will fix it  I went through a lot of answers on StackOverflow and nothing fixed it  I thought file is corrupted   from datetime import datetime jsts   1468629431 0 datetime fromtimestamp jsts

User · Answer

Since there s a chance that your excel files are coming from different computers people  there s a chance that the formatting is messy  so be extra cautious   I just imported data from 50 odd excels where the dates were entered in DD MM YYYY or DD-MM-YYYY  but most of the Excel files stored them as MM DD YYYY  Probably because the PCs were setup with en-us instead of en-gb or en-in     Even more irritating was the fact that dates above 13 MM YYYY were in DD MM YYYY format still  So there was variations within the Excel files   The most reliable solution I figured out was to manually set the Date column on each excel file to to be Plain Text -- then use this code to parse it   if date str from excel      try          return datetime strptime date str from excel    d  m  Y       except ValueError          print  Unable to parse date

User · Answer

Incase you re using pandas and your read excel reads in Date formatted as Excel numbers improperly and need to recover the real dates behind     The lambda function applied on the column uses xlrd to recover the date back  import xlrd df  possible intdate     df  possible intdate   apply lambda s  xlrd xldate xldate as datetime s  0      gt  gt  df  possible intdate       dtype   lt M8 ns

User · Answer

xlrd xldate as tuple is nice  but there s xlrd xldate xldate as datetime that converts to datetime as well   import xlrd wb   xlrd open workbook filename  xlrd xldate xldate as datetime 41889  wb datemode    gt  datetime datetime 2014  9  7  0  0

User · Answer

When converting an excel file to CSV the date time cell looks like this   foo  3 16 2016 10 38  bar   To convert the datetime text value to datetime python object do this   from datetime import datetime  date object   datetime strptime  3 16 2016 10 38     m  d  Y  H  M        excel format  CSV file    print date object  will return 2005-06-01 13 33 00

User · Answer

You can use xlrd   From its documentation  you can read that dates are always stored as numbers  however  you can use xldate as tuple to convert it to a python date   Note  the version on the PyPI seems more up-to-date than the one available on xlrd s website

User · Answer

For quick and dirty   year  month  day  hour  minute  second   xlrd xldate as tuple excelDate  wb datemode  whatYouWant   str month      str day      str year

User · Answer

Expected situation     Wrong output from cell values   42884 0    Expected output 2017-5-29   Example  Let cell values 2 2  from sheet number 0 will be the date  targeted  Get the required variables as the following  workbook   xlrd open workbook  target xlsx    sheet   workbook sheet by index 0   wrongValue   sheet cell value 2 2    And make use of xldate as tuple  y  m  d  h  i  s   xlrd xldate as tuple wrongValue  workbook datemode  print   0  -  1  -  2   format y  m  d     That s my solution

User · Answer

Please refer to this link  Reading date as a string not float from excel using python xlrd  it worked for me    in shot this the link has    import datetime  xlrd book   xlrd open workbook  myfile xls   sh   book sheet by index 0  a1   sh cell value rowx 0  colx 0  a1 as datetime   datetime datetime  xlrd xldate as tuple a1  book datemode   print  datetime   s    a1 as datetime

[python] How do I read a date in Excel format in Python?

Examples related to python

Examples related to excel

Examples related to datetime