Forum Discussion
Converting h:mm:ss.000 to decimal seconds
Dear Support Team,
On my MacBook Air, in Excel, I have the following setup:
- Column B: Time shown as "Custom" format of h:mm:ss.000 (ex. 12:23:45.567)
- Column C: Converts that cell to decimal seconds with formula: =B1*86400 (Result: 44625.567)
- All works fine, as expected.
On my new Mac Studio:
- I open the same file ... but column A (the time) is being read as text !! (content displayed left-aligned). The formula does not return a result, and shows #VALUE!
- If I remove the three decimal places (.567), and make it 12:23:45, then the formula works !!
QUESTION: I have tried so many things, and still cannot figure out why on this device, Excel is not accepting the three decimal places ??
Would appreciate your help ...
Thanks
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-
7 Replies
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?
