Adding time in two columns

[Santo21]

I download a report from a central server in Excel format which has names and time to show total worked hours. In this report I need to add time in two columns to get the total hours.

Here is the example:
In the first column the time is given as :18:44 and in the second column :27:04. If I use the formula =SUM(a2:b2) it gives 0:00:00. But when I manually add a 0 before the time ie 0:18:44 & 0:27:04, it adds and shows result as 0:45:48. I have attached a screenshot for clarity.

Excel Scr Shot.JPG

Now to get the total time, I add 0, which takes longer time. Is there a way to get the total time in much easier way? Please help.

[martindwilson]

try
=SUM((0&A1),(0&B1))
hmm
:18:44
is that hours:mins
or min:sss

[Santo21]

Some one suggested to use =SUM(TIMEVALUE("0"&A2),TIMEVALUE("0"&B2))

This formula works when the time is :18:44 & :27:04 (mm:ss). But for certain employees, the time is 1:15:54 (hh:mm:ss) and :14:33 (mm:ss) or it can be in mm:ss and hh:mm:ss format. So, is there a way to make sure that when I calculate the total worked hours, the formula takes care of this?

Please do suggest.

[martindwilson]

a sample of your raw data would be helpful
Attach a sample workbook. .

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Santo21]

I have attached the Excel file for your reference. Please do check and let me know how this can be solved. I appreciate your help.

[boopathiraja]

iN E2 and Drag it down

Please Login or Register  to view this content.

[Santo21]

Thanks to martindwilson & boopathiraja for helping to answer my query.

Formula given by boopathiraja fixed the issue. Thanks for your help.

Keep up your good work.