Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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!


Re: Time Conversion

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


Re: Time Conversion

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


Re: Time Conversion (example)

Posted by Daniel on May 02, 2000 12:26 PM
MY EXAMPLE: (formatted)

Start End # of Minutes Hours
0758 1605 487 8.12




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.