Time Conversion
Posted by Jonathan on May 01, 2000 12:37 PM
I want to create a spreadsheet that will do the following:
1) Convert time from hours & minutes to 1/100 hours.
2) Add the results.
Here is an example to clarify: Joe Smith clocks in at 7:58 am and then clocks out at 4:05. If we convert the time to 1/100s, it is 7.97 and 16.08, for a total of 8.11 hours.
How can I create a formula that will convert the time from 7:58 to 7.97, 4:05 to 16.08, and so on?
I thought this would be simple until I actually sat down and tried it!
Posted by Celia on May 01, 2000 4:06 PM
Johnathan
If you have 7:58AM in cell A1 and 4:05PM in B1,
format cell C1 as General and enter the following formula : =24*(B1-A1)
There is some good info re workimg with times at http://www.cpearson.com/excel/datearith.htm
Celia
Posted by Daniel on May 02, 2000 12:24 PM
If your question was already answered then disregard...
MY EXAMPLE:
Start End # of Minutes Hours
0758 1605 487 8.12
(7:58) (16:05)
Here is something that I've played around with. I'm sure this isn't the easiest or best way but it has worked for me in the past.
(note example above)
For easier data entry AND in order for the formula to work I formatted the "Start" and "End" columns as text. Each time is entered in military time and ALL entries are four digits. The idea is converting each time into minutes and subtracting them. For "# of minutes", I entered...
=((((MID(C5,1,2))*60)+(MID(C5,3,2)))-(((MID(B5,1,2))*60)+(MID(B5,3,2))))
EXPLANATION OF FORMULA:
"MID(C5,1,2)*60" - takes hours of "End" time (C5)and converts to minutes.
"+(MID(C5,3,2)) - adds the minutes of the "End" time.
The same operation is done to the "Start" time and it is subtracted.
My last column, "Hours", equals "# of Minutes" divided by 60.
Hope this helps or gives you some ideas.
Daniel
Posted by Daniel on May 02, 2000 12:26 PM
MY EXAMPLE: (formatted)
Start End # of Minutes Hours
0758 1605 487 8.12
