Forum Discussion

Copper Contributor

Jul 30, 2022

Solved

Time Conversion

Hi All, I'm having trouble working out how to convert a time. I have 1 minute 30.009 seconds in one cell, entered as 1:30.009 and want to display is another cell as 90.009 seconds. any help wo...

excel

Riny_van_Eekelen
Jul 30, 2022
forbesy71 The simplest "formula" in C1 would be =A1 and than set the custom format for C1 to [ss].000

Alternatively, you could use the TEXT function that allows you to type the desired format into the formula between quotation marks. Using a double hyphen (i.e. two minus signs) in front forces it to become a numeric value.
= --TEXT(A1,"[ss].000")
forbesy.xlsx10 KB

forbesy71

Copper Contributor

Jul 30, 2022

Thanks for the reply, i have done that but i'm after the formula or steps to do it as below

Riny_van_Eekelen

Platinum Contributor

Jul 30, 2022

forbesy71 Sorry, but I don't understand what you mean by "I'm after the formula or steps to do it".

forbesy71
Copper Contributor
Jul 30, 2022
Sorry for the confusion,

What I want is, if i enter 1:30.009 into cell A1, i want it to display in cell C1 as 90.009 automatically.

Hope this explaination helps.
- Riny_van_Eekelen
  Platinum Contributor
  Jul 30, 2022
  forbesy71 The simplest "formula" in C1 would be =A1 and than set the custom format for C1 to [ss].000
  
  Alternatively, you could use the TEXT function that allows you to type the desired format into the formula between quotation marks. Using a double hyphen (i.e. two minus signs) in front forces it to become a numeric value.
  = --TEXT(A1,"[ss].000")
  forbesy.xlsx10 KB
  - forbesy71
    Copper Contributor
    Jul 30, 2022
    Thank you Riny, that's got it, I didn't have the brackets around the ss in the formating.
    Muchly appreciated