Forum Discussion
Converting h:mm:ss.000 to decimal seconds
Issue FIXED !!!
Believe it or not, it was closely linked to your earlier comment about what was being used as a "delimiter" !
Although the settings in Excel were correct, and there system settings box was "unchecked" ... I decided to check the System Settings of the Mac Studio device. Reminder that I mentioned it was new.
Well, the Region Settings were set to Switzerland, which is where I am, so I can explain that bit ! :))
Once I switched it to UK, or even USA, the formula works properly, and the trailing decimals, beyond "ss.000" are being displayed properly !!
So thank you for at least directing my mindset in the correct direction.
Much appreciated.
R-
Much appreciate the quick response.
It's in fact the "identical" file, opened on two separate machines ...
> On the MacBook Pro ... the file opens and the calculation cell correctly converts the time (hh:mm:ss.000) formatted number, Ito seconds.
> On the Mac Studio (a brand new installed device and brand new Excel installation), the cell simply does not calculate and gives me the #VALUE! error in the cell.
I have checked the various Excell settings, but I don't see a difference !
In fact, even on the MacBook Pro, I never did any changes to settings, etc.
Once I formatted the cells containing the time as "Custom" format and defined the format (hh:mm:ss.000) ... the subsequent cell formula (=B1*86400) did the job in converting it into seconds.
But for some reason, it is n to working on the Mac Studio Excel !!
Please try the following on the MacBook Pro:
Select the cells with a time with decimal seconds.
Set the number format to General.
What do you see? Numbers, or still time values?
Not sure I understand the instruction, but https://www.dropbox.com/scl/fi/5z2mtied8crhhvv7rlg3j/Time-Conversions.xlsx?rlkey=h59gye7bsxcweiq5uo6f0fbqv&dl=0 is the file. Please give it a try on your machine.
In the attached screenshot, notice that when I open the file on the Mac Studio and press return on the RACE (sec) cell, which holds the formula, Excell cannot execute the formula and returns an error.
The file is fine ... as I have been using it for years !!
But for some reason, on the new installation of Excel, on this new Mac Studio machine, the formula is not executing.
IMPORTANT: If I remove the three decimal places after the "ss", then the result IS shown !!
If I include the decimal seconds, it fails !!Thanks. In the screenshot, the values in column B are left-aligned.
If I set the number format to General, I still see values that look like time, instead of numbers:
This means that the values are not real time values, but text. At least, that is how Excel sees them.
Fortunately, there is an easy way to convert them to real time values:
- Select the values.
- On the Data tab of the ribbon, click Text to Columns.
- Click Finish.
As you can see, the times are now right-aligned:
Another way to do the same:
- Select an empty cell and copy it.
- Select the cells with the "time" values.
- Click the lower half of the Paste button and select Paste Special...
- Alternatively, right-click in the selection and select Paste Special...
- Select Add, then click OK.
- If necessary, apply the number format hh:mm:ss.000 again.
@Hans Vogelaar
BTW ... to answer the earlier question ... on both machines ... under Preferences > Edit ... the "Use System Separators" is UNCHECKED, and the decimal separator is set to "."
(Needless to say, thank you for the follow-ups and trying to assist)Issue FIXED !!!
Believe it or not, it was closely linked to your earlier comment about what was being used as a "delimiter" !
Although the settings in Excel were correct, and there system settings box was "unchecked" ... I decided to check the System Settings of the Mac Studio device. Reminder that I mentioned it was new.
Well, the Region Settings were set to Switzerland, which is where I am, so I can explain that bit ! :))
Once I switched it to UK, or even USA, the formula works properly, and the trailing decimals, beyond "ss.000" are being displayed properly !!
So thank you for at least directing my mindset in the correct direction.
Much appreciated.
R-