Cell format mess

[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.
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 and turn those seconds into hours and minutes (decimal)?



Any ideas?

Thank you very much.
Karlheinz

 

[p45cal]

How are you importing those data?
If you're pasting them in from a copy operation then try, before pasting, formatting the range they're going to go in as Text. In this case A1:E81, then paste.
That way, Excel shouldn't try to be helpful. (Those dates are probably Excel trying to be helpful.)
After that we're free to convert these data properly.

So, let's say you manage to get the data all in as numbers, consistently and correctly, then you can continue to display the values (seconds) as seconds by sticking to the General format, or if you want to see hours, minutes, days too, you can convert these seconds values to days (the usual way Excel handles date/times) by dividing by 86400 (that's the number of seconds in a day). Then you can format those cells in various ways:
If you choose to use the custom format d \d\a\y\s hh:mm:ss you'll see your 442108 seconds as 5 days 02:48:28 (fortunately your days don't seem to greater than 31), but if you use the General format for these cells you'll just see the number of decimal days. If you wanted to see total hours and minutes you could format as [h]:mm yielding 122:48 for that value.

The most important question, though, is the first one: How are you importing these data, and how best to do that depends on the source of that data. There's hundreds of ways of getting data into Excel. Power Query (built-in to Excel since Excel2016) is a newish and robust way to get data into Excel consistently and may be a good candidate to use to bring your data in..
So what is the source?

 

[JoePublic]

Please note cross-posting rules, here and elsewhere.
Cross-posted: https://www.mrexcel.com/board/threads/cell-format-mix.1263691/

 

[Karlheinz]

How are you importing those data?
If you're pasting them in from a copy operation then try, before pasting, formatting the range they're going to go in as Text. In this case A1:E81, then paste.
That way, Excel shouldn't try to be helpful. (Those dates are probably Excel trying to be helpful.)
After that we're free to convert these data properly.

So, let's say you manage to get the data all in as numbers, consistently and correctly, then you can continue to display the values (seconds) as seconds by sticking to the General format, or if you want to see hours, minutes, days too, you can convert these seconds values to days (the usual way Excel handles date/times) by dividing by 86400 (that's the number of seconds in a day). Then you can format those cells in various ways:
If you choose to use the custom format d \d\a\y\s hh:mm:ss you'll see your 442108 seconds as 5 days 02:48:28 (fortunately your days don't seem to greater than 31), but if you use the General format for these cells you'll just see the number of decimal days. If you wanted to see total hours and minutes you could format as [h]:mm yielding 122:48 for that value.

The most important question, though, is the first one: How are you importing these data, and how best to do that depends on the source of that data. There's hundreds of ways of getting data into Excel. Power Query (built-in to Excel since Excel2016) is a newish and robust way to get data into Excel consistently and may be a good candidate to use to bring your data in..
So what is the source?

Thank you for the reply.
The report comes out of Gensys Purecloud as a csv file.
I will try your suggestions. Thanks again

 

[p45cal]

The report comes out of Gensys Purecloud as a csv file.

Right! Then attach a copy of the csv file but, and this is important, you must never have even looked at this file with Excel. Even if you don't save it (Excel sometimes makes changes to such files).