changing date formats

[chris.howes]

i need to process data in excel from a 3rd party report that gives th
timefor different departments in the format dd:hh:mm:ss and i need t
be able to get excel to work out the time in hours and minutes so i ca
analyse it further
thnx in advanc

 

[Ron Rosenfeld]

i need to process data in excel from a 3rd party report that gives the
timefor different departments in the format dd:hh:mm:ss and i need to
be able to get excel to work out the time in hours and minutes so i can
analyse it further
thnx in advance

=LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))

and format as [h]:mm


--ron

 

[Larry Daugherty]

If there's a more elegant solution would someone please post it?

I would create a new column "Seconds" with a long integer data type.
Convert each of the values in the existing time field to seconds and sum
into the new Seconds field. You can then do your machinations on seconds
and finally convert back to the correct time units.

This sounds like something that would be duck soup for John Walkenbach and
his Power Utility Pack. It's a question that comes up quite often. You
should be able to track John via a google search.

HTH