Converting time with Python and DataNitro in Excel

Question

I have a list of times in h:m format in an Excel spreadsheet, and I'm trying to do some manipulation with DataNitro but it doesn't seem to like the way Excel formats times.

For example, in Excel the time 8:32 is actually just the decimal number .355556 formatted to appear as 8:32. When I access that time with DataNitro it sees it as the decimal, not the string 8:32. If I change the format in Excel from Time to General or Number, it converts it to the decimal (which I don't want). The only thing I've found that works is manually going through each cell and placing ' in front of each one, then going through and changing the format type to General.

Is there any way to convert these times in Excel into strings so I can extract the info with DataNitro (which is only viewing it as a decimal)?

score 3 · Accepted Answer · answered Dec 05 '12 at 14:42

3

If .355556 (represented as 8:32) is in A1 then =HOUR(A1)&":"&MINUTE(A1) and Copy/Paste Special Values should get you to a string.

answered Dec 05 '12 at 14:42

pnuts

58,317
11
87
139

1

Thanks, seems to be what I need! – user1104854 Dec 05 '12 at 14:45
that actually might work better. I won't have to use regular expressions to separate the hours from minutes. I can just take the int and convert the decimal to minutes. – user1104854 Dec 05 '12 at 16:30

score 0 · Answer 2 · edited May 23 '17 at 12:29

Ideally it seems that you probably don't want to change the way that excel keeps the data (Obviously depending on your use case).

If that is the case there is excellent post How do I read a date in Excel format in Python? that explains how to convert the float to a python date time object. Specifically the script by @John Machin works great.

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)
        )

Note his disclaimer "Here's the bare-knuckle no-seat-belts use-at-own-risk version:" I have used it with no problems.

Converting time with Python and DataNitro in Excel

2 Answers2