Forum Discussion

Myersie's avatar
Myersie
Copper Contributor
Jun 17, 2022

=--TEXT(D2,"00\:00\:00\.00") Formula

Hi Folks,

I am using the formula =--TEXT(D7,"00\:00\:00\.00") to change a text string of numbers into a time recorded on a stopwatch to do elapsed time time calculations.

On my ipad this means an input of 0593882 gives 0:59:38.83 as the watch time, and all calculations work as they should.

However, when I open the file on my Windows laptop and change the input string this results in a message #VALUE instead of the time. This happens to every string i change.

When I re-open on my ipad and change an input the watch time appears again.

Can any offer any suggestions as to why this happens? (Both apps are used in my 365 subscription).

thanks

excel
Formulas and Functions

3 Replies

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Jun 17, 2022

    Myersie 

     

    I suspect that the regional settings for the two computers are different.

     

    In particular, for the iPad, the decimal point is a period; but for the laptop, the decimal point is a comma.

     

    Moreover, I suspect that the formula appears to be =--TEXT(D7;"00\:00\:00\.00") on the laptop.  Note the semicolon for the parameter (list) separator.

     

    As proof of concept, change the formula to =--TEXT(D7;"00\:00\:00\,00") on the laptop.  Note the comma for the decimal separator.

     

    I don't know why the regional settings would be different.  So I don't know if the "right" solution is for you to change the regional settings on one device or the other; or for you to change the formula on the laptop as indicated.

     

    Alternatively, perhaps the issue has something to do with the Office 365 settings.  I am not familiar with the subscription service.

    • Myersie's avatar
      Myersie
      Copper Contributor
      Jun 19, 2022
      Joe
      I managed to find the solution to this issue after some trial and error. It appears that =--TEXT(D7,"00\:00\:00") works fine, but =--TEXT(D7,"00\:00\:00\.00") does not. The latter changes the string to a text version of the time, while the former gives a time.
      I fiddled around in a blank spreadsheet, where it was easier to see what was happening. It means a few extra columns to do the calculations for the 1/110ths of a second, now but it works ok
    • Myersie's avatar
      Myersie
      Copper Contributor
      Jun 17, 2022
      Thank you Joe, I shall try your suggestions later tonight 👍🏻