General format to Time format

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello all,
I have a column of data that represent time amounts, ex:

a1 123:00 ( which reads 123 hours )
a2 1,234:30 ( which reads 1,234 hours and 30 minutes )
a3 00:15 ( which reads 15 minutes )

I check the formatting and they are all "General"

My challenge is:
To take the above data and convert to an actual "Time"
format so that I can sum it up.
With the hopes that the final solution would show a total
of 1,357:45 ( in a "Time" format reflecting 1,357 hours
and 45 minutes )

Thanks in advance for any assistance provided
Joe
 
Hi
try (the values are probably currently sored as text)
=LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,10)/(24*60)

anf format the resulting cell with the custom format [hh]:mm
 
Back
Top