How to add hours and minutes within a cell?

Question

I am inserting time punches in a row and the final column is HOURS. I need to add 2:43+0:23+3:17+0:58+1:13 within the cell. So that Total Hours are in that cell.
I have tried to find a way to add these but all formulas are supposing that the times are in multiple cells. I need a way to get the total without having the hh:mm in another cell.

Answer 1

Hi @Michele Zierdt

To facilitate calculation, it is recommended that you split each time into different cells.

If the number of times in a cell is fixed, such as the example you provided, the number of times is 5, you may try following steps.

• Go to Formulas tab > Define Name, create a new name. As the data is in H5, the formula is =EVALUATE("{"&SUBSTITUTE(SUBSTITUTE(Sheet1!$H$5,"+",";"),":",".")&"}"), this action will temporarily converts the string to an array.
• Then use the formual =TEXT(SUM(VALUE(SUBSTITUTE(INDEX(Array,1),".",":")),VALUE(SUBSTITUTE(INDEX(Array,2),".",":")),VALUE(SUBSTITUTE(INDEX(Array,3),".",":")),VALUE(SUBSTITUTE(INDEX(Array,4),".",":"))),"hh:mm") to get the sum.

Please note:

=SUBSTITUTE(INDEX(Array,1),".",":") gets the first data text, "Array" is the name defined as above.

=SUBSTITUTE(INDEX(Array,2),".",":") gets the second data text and so on.

The file need to be saved as .xlsm, since EVALUATE is a macro function.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---