Excel Convert Datetime Stamp to HH and MM Worked

[CeeJay4]

I am working with a spreadsheet dump from an outside application, and all "time worked" columns automatically output the data in a Datetime stamp format - eg. 1/1/1900 12:52:00 AM. Somehow, in this specific cell, it is displaying 24:52:00, with a custom format of [h]:mm:ss. I am attempting to convert the datetime fields to hours and minutes worked, taking into consideration that many "hh" will be over 24. I can get there using about 14 helper fields, but there must be a better way! Please tell me there is a better way! Thanks!

 

[Becky]

Just so I know what you're asking... you want to change the cell format so it displays it differently?

 

[CeeJay4]

Yes... I am attempting to convert the field to hours and minutes worked, taking into consideration that many "hh" will be over 24.

 

[CeeJay4]

I need the output to be in a true [h]:mm format so that I can perform many more calculations on this new field.

 

[Becky]

If you need to use the figures for calculations, would converting the hours/minutes worked to decimal help? Just multiply by 24, and make sure that your formatting is set to number.

 

[CeeJay4]

Let me give that a go. I'll touch base with you after I try it. Thank you!!

 

[CeeJay4]

It's a beautiful thing! Thank you, thank you, thank you!!

 

[Becky]

Ah brilliant, I'm pleased to hear that worked for you! Thanks for letting us know