Correcting the text format 00:14 to time format hh:dd:mm and giving total time as result

[aria_1111]

Hi,

Greetings!

I have to pull avaya cms report daily in csv format and then i import it to excel. when i do it, it shows the login hours and lunch breaks, comfort break, medical break taken in format 00:00. Entire sheet contains such numbers then.
Data imported is in this format on entire sheet

[TABLE="width: 260"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]dinner break[/TD]
[TD]lunch break[/TD]
[TD]comfort break[/TD]
[/TR]
[TR]
[TD]0:01[/TD]
[TD]21:00[/TD]
[TD]14:01
[/TD]
[/TR]
</tbody>[/TABLE]
---above data is in text format as improted in excel from csv file using text delimiters

Problem :to get total of all breaks i need to correct its format to proper time format hh:mm:ss. I tried using find "0:" and replace with "00:" but it changed the entire data and resulted incorrect data.

please tell me if there is any other way to get above mentioned breaks data (on excel sheet) in time format hh:mm:ss.

Also how can i get a total of these three breaks combining all hh:mm:ss.

result should be like
[TABLE="width: 324"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]dinner break[/TD]
[TD]lunch break[/TD]
[TD]comfort break[/TD]
[TD]total breaks[/TD]
[/TR]
[TR]
[TD="align: right"]0:00:01[/TD]
[TD="align: right"]0:21:00[/TD]
[TD="align: right"]1:14:01[/TD]
[TD="align: right"]1:35:02[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
aria

 

[pgc01]

Hi aria

I understand the dinner and lunch breaks, you have the value is in hours and minutes and it should be minutes and seconds, which is just divide by 60.

I don't understand, however, the comfort break, how do you get from 14:01 to 1:14:01? where does that 1 come from?