Excel sums up hours but no minutes

[gustavodz]

Hello Everyone,
First time posting here and looking for your expert help.
I'm trying to sum times and I'm having a hard time getting it right,
I'm importing some data from an ACD software in my call center, I'm getting times that my employees spend on break & lunch but I've found that excel only adds times that are over an hour and ignores the times that are less than 1 hour, I've tried formating cells to [h]:mm:ss
but that works only with hours ex, column A :57:25 column B 1:03:00
then column C =SUM(A1:B1) but I get only 1:03:00 where it should be 2:00:25
seems to work if I add a 0 (0:57:25) but frankly it's a big datasheet to modify manually every entry that involves just minutes.
I have also noticed that for example times exceding an hour are formated as times (1:03:00 is displayed as 1:03:00 AM) but minutes don't show any formatting.
Can you please provide some advice on how to sum both kind of times, is there any special formatting involved?
I can upload a sample worksheet I just dont know how.
Thanks in advance!

 

[murrayty]

Can't add text to a time... I am actually amazed it is giving you a number, should be giving you a value error.

You will have to change the minute only entries to a time format. If it is such a large sheet this might help.

=IF(ISTEXT(A1),"0"&A1,A1)

That will add a 0 at the beginning if it isn't a date entry. Then you can copy paste values and will have it in a time format.

 

[AlphaFrog]

This would work as well...
=(0&A1)*1

It would add a zero in front of all entries; text or time. The time entries would just ignore the leading zero.

Format the column with the formula as time "37:30:55".

 

[gustavodz]

Thank You, =(0&A1)*1 did the trick.
I can move on now....