SOLVED

Converting h:mm:ss.000 to decimal seconds

Copper Contributor

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

7 Replies

@RK32180 

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 !!

@RK32180 

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?

@Hans Vogelaar 

Not sure I understand the instruction, but here 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 !!Screenshot 2023-09-17 at 20.47.58.png

@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)

@RK32180 

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:

HansVogelaar_0-1694979336879.png

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:

HansVogelaar_1-1694979532390.png

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.
best response confirmed by RK32180 (Copper Contributor)
Solution

@Hans Vogelaar 

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-

1 best response

Accepted Solutions
best response confirmed by RK32180 (Copper Contributor)
Solution

@Hans Vogelaar 

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-

View solution in original post