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!
1 Answer
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
andMID
) 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 callingVALUE
. - 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 theTIME
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. Jan 23, 2013 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! Jan 23, 2013 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 valueI1
is(DAY(I1)*24 + HOUR(I1))
hours plusMINUTE(I1)
minutes. Jan 23, 2013 at 22:30