1

I have a python script that collects a bunch of data. At the end, it prints the collected data to an Excel spreadsheet. One of my columns is displaying the "Time Elapsed" in a form of minutes:seconds. My python script has already given the entire column a "Custom" number format of mm:ss, and provides all the data in such form.

For some reason though, I can't seem to get any functions to work with this data, unless I go into the cell and press Enter. Now, turning on "Show Formulas". I see that the cell is not a time at all to Excel, but instead a decimal number, and I can't figure where it gets it from. After pressing enter and getting the decimal, functions work with the data.

I'm probably being dumb here.. but can someone tell me how Excel converts 1:49 (1 minute:49 seconds) to 0.075694444444? I also see that after pressing Enter in the cell, it converts 1:49 to 1:49:00 AM, which is not applicable to my situation.

I'm really sorry if I worded this poorly. Any thoughts / ideas are welcome.

Thanks!

| improve this question | | | | |
  • 1
    I'll answer the decimal question. Time is stored as decimal with 1 day (24 hours) = 1. So one hour is 0.042 one minute is 0.00069. So the decimal you gave above is 1 hour +49/60 of an hour or 1:49am – gtwebb Dec 22 '14 at 21:10
  • Thanks @gtwebb, that helps :) Still working on this Hannu. Your suggestions unfortunately haven't helped, though I'm sure it's probably the string thing, which is in your comment, not answer. – user2221125 Dec 23 '14 at 17:43
2

You could also consider putting the time function into the excel cells to ensure it comes in correctly.

Instead of putting in 01:49 or 00:01:49 put in =time(0,1,49) and it is guaranteed to treat it as a time and not a string (still format it as mm:ss for correct display).

Alternatively calculate the decimal time in your script and put that in the cell.

1/24/60 + 49/24/60/60 = 1 minute 49 secs.

Depending on how you have time stored in a variable this could result in having to parse the data but would get around your issue of things coming in as a string.

| improve this answer | | | | |
  • This helped me the most, I was able to easily split the string in python (to get mins:secs) and use the time function. Thanks! – user2221125 Dec 26 '14 at 15:58
0

To be safe, have your Python script prepend "00:" for HOURS and you will probably have less trouble getting Excel to understand.

That will initially display as "12:01:49 AM" for "00:01:49" but all you have to do is change the display format for it.
How: Press and hold CTRL and hit 1, then find and click "Time" (or is it Date in Excel?) and then the suitable format. As per usual you can do this for an entire column or row in one go.

Excel uses 1.0 as value for 24 hours for date and time (type 1.0 in any cell the format it as Time/Date to verify it).

| improve this answer | | | | |
  • Thanks Hannu. I did your suggestion. Unfortunately, I'm still having an issue with having to individually click the function bar for each cell and pressing enter before anything works. I googled this particular issue, and have made sure that calculations are set to auto. Also, pressing F9, similarly Shift+F9, etc, doesn't resolve this. I was hoping with the decimal question, that I could do the same conversion excel does in my script, and then have it put that decimal in the cell instead, in hopes it liked that better. – user2221125 Dec 22 '14 at 20:33
  • Make sure you have NO SPACES nor quotes included in the field content. That would make Excel interpret it as something to be kept as "Text" which disables the normal auto-sensing of data format. – Hannu Dec 22 '14 at 20:36
  • decimalTime=(hours*24*60+minutes*60+seconds)/(24*60*60) may be a help to understand the time storage format. The words here are to be interpreted as Python variables. – Hannu Dec 22 '14 at 20:40
  • I think that might actually be my problem. I think it's writing the time as a string to excel, and I have to press enter to convert it to a time format. I'll investigate further, thanks for that! – user2221125 Dec 22 '14 at 20:49
  • If you find the anwer helpful, please mark it as such with the tick at the number above left. – Hannu Dec 22 '14 at 20:52
0

I've got similar problems in the past. Hannu's explanation and option are good. Another solution: try to format the cell as text before you fill it, save the spreadsheet and then fill the values formatted as text. Not pretty, but practical.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.