Forum Discussion

Mike Button's avatar
Mike Button
Copper Contributor
Sep 15, 2017
Solved

Having an Issue with Excel hour/time formatting

0 down vote https://stackoverflow.com/questions/46245285/having-an-issue-with-excel-hour-time-formatting#
 

I have a column that shows time in hours as example of

19.77

2.04

0

0.19

69.06

0.03

0.92

9.32

 

If I format the column as time format h:mm the time typically converts, but there is some that will not convert at all.

This works 19.77 turns to 20:17 This works 105.22 turns to 105:22 These values will not gracefully move to hours 92:73 144:88 25:73

THere are more as well

So my question is why does the majority of them convert properly while others don't. This is a CSV file so no preformated cells before I start

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 16, 2017

    Another way:

    1) Insert new empty column (L) next to your column with time (K) and format it as [h]:mm

    2) Into new column add formula

    =K2/24

    and drag it down

    Here you have correct elapsed time now

     

    And in attached file

     

13 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 15, 2017

    Hi Mike,

     

    I'm not sure how you receive such results, if you attach small sample it'll be more clear. You have some numbers in your CSV file, let take 19.77. In Excel integer part of the number is the day there 1 is Jan 01, 1900. Decimal part is the time.

    If you format 19.77 as h:mm it shall return 0.77 from 24 hours, or 18:28. Same result will be for 1.77, etc.

     

    In full date/time 19.77 means 1900-01-19 18:28

     

    If you format as [h]:mm that will be without the days elapsed time: (19+0.77)*24 Hrs, or 474:28

     

    • Mike Button's avatar
      Mike Button
      Copper Contributor
      Sep 15, 2017

      Here is a temp version of it. 

       

      But another things comes into play then. The numbers represent hours. So for example 94.88 would be 94 hours and 88 percent of an hour. 88 does not signify minutes. Therefore the time would be 94.53 hours

      Thinking more about this is there an equation where I can translate 94.88 to actual hours of 95 hours and 53minute. Essentially the 94 stays the same while the 88 is a percentage of the full hour.

      • Mike Button's avatar
        Mike Button
        Copper Contributor
        Sep 15, 2017

        I never said I was a pro LOL, but here is a very messy way of doing what I think I need to.

        1) created 4 new colums

        2) Text to columns, using "." as a deliminter on the cell with the hours (ex: 92.73)

        3) Took 60 and multipled 73% (from example above answer is 44.6)

        4) Took the 44.6 and then did =round(44.6,0)  This allowed me to remove the "."

        5) last column added it all together =K2&"."&N2  answer is 92.44

         

        Like I said, ugly but has to be a better way.

         

Resources