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
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))
I hope this is enough that you can adapt it to your needs.