# working with elapsed time in Excel 2010

I have an excel spreadsheet in which employee times have been recorded with text and numbers, as in 7 hours 6 minutes. I need to convert the existing data to h:mm so I can then deduct lunch breaks automatically. Any advice appreciated!

-

Standard advice for problems like this is not to do a lot in a single formula (in a single cell), but rather to spread the work out over several cells.  Typically inputs and results will be in parallel columns; so the recommended approach is to calculate intermediate results in other columns, which are commonly called “helper columns”.  Once you have everything working, you can hide the helper columns, and/or you can put them out of sight by putting them way to the right of the main data (e.g., columns `AA``AG`).

Here’s something I threw together:

```A1: 7 hours 6 minutes
B1: =FIND("hours", A1)
C1: =FIND("hour", A1)
D1: =FIND("minute", A1)
E1: =VALUE(LEFT(A1, C1-1))
F1: =VALUE(IF(ISERROR(B1), MID(A1, C1+4, D7-(C1+4)), MID(A1, B1+5,D1-(B1+5))))
G1: =AND(F1>=0, F1<60)
H1: =IF(OR(ISERROR(C1),ISERROR(D1),ISERROR(E1),ISERROR(F1),ISERROR(G1)),TRUE,NOT(G1))
I1: =IF(H1, "Invalid", TIME(E1, F1, 0))
```

Column `I` contains the result you want.  (You probably already know this, but, if it displays as a number, it is a fraction of a day.  You can get it to display as hh:mm by formatting it.)  Some explanations:

• Search for the strings we expect to see.  Search for “hour” and “minute” to allow entry of “1 hour 30 minutes” or “5 hours 1 minute”.
• Extract (with `LEFT` and `MID`) the pieces of the input string that we expect to contain the numbers.  Evaluate them (turn them into numbers we can do arithmetic with) by calling `VALUE`.
• If there is an error, display “Invalid”, otherwise, compute the time duration from the number of hours and the number of minutes.

Notes:

• It’s flexible (apathetic) enough that it will accept “1 hours 30 minute” or “5 hour 1 minutes”.
• It will report “Invalid” if the number of minutes is < 0 or ≥ 60, but it will throw a `#NUM!` error if the number of hours is < 0, and, if it’s ≥ 24, it treats it modulo 24 (because that’s what the `TIME` function does).
• Non-numeric text where the numbers should be (“FOO hours BAR minutes”) will cause an error, but non-integer numbers will not – “7.5 hours 0 minutes” is treated as “7 hours 0 minutes” (because that’s what the `TIME` function does).
• It will report “Invalid” if it doesn’t find the strings “hour” and “minute”, so “7 hours” will cause an error (use “7 hours 0 minutes”), but it ignores extraneous text after the word “minute”, so “7 hours 6 minutes until I can go home” is OK.

I hope this is enough that you can adapt it to your needs.

-
Wow, ok Scott, thanks for your details. I'm gonna give this a try tomorrow with a fresh mind, I really appreciate your taking the time to write this all out. – Lisa Starr Jan 23 '13 at 3:10
Scott, what you suggested worked! Wonderful! So now I have rows of numbers in a spreadsheet that reflect the hours worked at 7:06. I added a column to deduct 30 minutes lunch break for shifts over 5 hours, so now I've got a cell that says 6:36. I've got 130 rows of these, double spaced. I want to now add all of the hours worked by a certain staff member, and the total by all staff members, for the month. But when I total these, it does not compute correctly. I've tried playing with the formats and I either get something like 16:03 or a fraction, both of which are wrong. Please help! – Lisa Starr Jan 23 '13 at 20:52
For simple display purposes, try selecting a time format that allows hours values ≥ 24. If you don’t see one, go into custom formats and change `h:mm;@` to `[h]:mm;@`. For calculation purposes, time value `I1` is `(DAY(I1)*24 + HOUR(I1))` hours plus `MINUTE(I1)` minutes. – Scott Jan 23 '13 at 22:30
Ok, will give it a try, MANY thanks. – Lisa Starr Jan 24 '13 at 0:13