Adding time in excel

Hi Kay
Thank you for your question in STL's forum. My name is Ron and I am one of STL's trainers.

I can see your dilemma. I just wanted to give you a quick answer to your query before I try to find a solution.

Firstly if the entries you want to add up are displayed the same way as you entered them in the query then you can't add that up because the entry is seen by Excel as text and we can't add up text values in Excel.

What you therefor firstly have to do is to Extract the numeric values from those text entries using either Flash fill, Text to columns or use complex combinations of text functions like Mid(), Left() and right(). After that you need to make the entries the same, so turn the hour values into minutes as well. So 2 hours 30 minutes becomes 150 minutes and all the other entries containing a hour value should be converted to minute values too so you get a column showing all the durations in minutes. Once you have done that you can simply add up the minute values to get a total.

There is no simple solution to your dilemma. But I hope this steers you in the right direction.

I added an Excel file to show you how you could approach the process. It contains comments to explain the parts of the process

Kind regards

Ron Oldeboom
Learning and Development Consultant
STL-training