3

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 1

1

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 AAAG).

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.

3
  • 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, 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!
    – Lisa Starr
    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 value I1 is (DAY(I1)*24 + HOUR(I1)) hours plus MINUTE(I1) minutes. Jan 23, 2013 at 22:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .