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-
Do you use a different decimal separator on the two machines, for example comma on one, and point on the other?
- Hi,
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 !!