Calculate Hours from Messy Text

I need to calculate hours given data like this in each cell: “9-10, 10-11, 10-11, 12-13, 12-15+, 14-17+

Download my Excel file with solution.


Messy Data

Data exported to Excel looks like this:










I need to calculate total hours in each cell. Cell A2 = 4 hours, cell A3 = 6 hours, etc.

There were 10 columns and 20000 rows (my sample is smaller). Solution needs to be easy to audit.



Initially it seemed daunting. I could use text-to-columns to split out each start/stop value and then a ton of formulas. Urgency of the request forced me to consider alternative methods. Two questions:

Is “+” symbol only used after 3 hour intervals? Are all other time intervals only 1 hour?

The answer was “yes” to both questions. I saw the pattern and solved it like this:

  1. Count the “+” symbols in each cell.
  2. Count the commas.
  3. Total of 1 hour time intervals
  4. Total of 3 hour time intervals
  5. Final total.







Sometimes an extra question saves a lot of unnecessary complexity and time!

Yes my solution is boring. No fancy dynamic arrays, vba, or power query. But if there’s a simple way to solve it shouldn’t we consider it? Especially when creating a solution that other people will use.

If the details were more complex (eg “+” didn’t exist, time increment could be any number) then I’d need a brute force method to subtract start time from end time. A ton of helper columns? Power Query? Mega formula?


About Me

My name is Kevin Lehrbass. I’m a Data Analyst and Excel enthusiast.

3 Comments Calculate Hours from Messy Text

  1. Pingback: Calculate Hours from Messy Text using Power Query | My Spreadsheet Lab

  2. Meganathan Elumalai

    Hi Kevin,
    Here is my solution, no additional Columns required.
    =SUM(LET(X,SUBSTITUTE(FILTERXML(“”&SUBSTITUTE(A2,”, “,””)&””,”//child::Text”),”+”,””),Y,IF(ISERROR(SEARCH(“-“,X)),TEXT(X,”dd-mm”),X),(RIGHT(Y,2)*1)-(LEFT(Y,2)*1)))


Leave a Reply

Your email address will not be published. Required fields are marked *