Cell format mix

[Karlheinz]

Hi everyone,

I've got a problem with some data I exported. The numbers in columns B through E are supposed to be seconds, but they look weird.
There are milliseconds added after the dots. Some of the values are lined up to the left and have the cell format "general".

How can I fix this and turn those seconds into hours and minutes (decimal)?

Any ideas?

Thank you very much.
Karlheinz

 

[RoryA]

The left-aligned ones are text. You didn't say how you actually got the data into excel so it's hard to be sure why some are text and some are not, but I'd suspect there are spaces or other invisible characters in there.
Excel treats dates as the number of days since 1 Jan 1900 (1 being 1st Jan 1900) and times are the decimal p0arts of days, so to convert those seconds into actual time values, you need to divide by 86400 (24hrs * 60 minutes * 60 seconds)

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Cell format mess

Hi everyone, I've got a problem with some data I exported. The numbers in columns B through E are supposed to be seconds, but they look weird. It looks like there are milliseconds added as well. Some of the values are lined up to the left and have the cell format "general". How can I fix this...

forums.excelguru.ca

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Karlheinz]

The left-aligned ones are text. You didn't say how you actually got the data into excel so it's hard to be sure why some are text and some are not, but I'd suspect there are spaces or other invisible characters in there.
Excel treats dates as the number of days since 1 Jan 1900 (1 being 1st Jan 1900) and times are the decimal p0arts of days, so to convert those seconds into actual time values, you need to divide by 86400 (24hrs * 60 minutes * 60 seconds)

Thank you for your reply.
The report comes out of Gensys Purecloud as a csv file.