Forum Discussion
Having an Issue with Excel hour/time formatting
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
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
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.
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.
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
Thank you, this was the fastest and easiest way. While the mround worked, this is the simpliest.
Mike,
this should work:
=MROUND(A1/24,1/24/60)
