Back to Dates in Excel archive index

Back to archive home

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!

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

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

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.

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.