Forum Discussion

falconpunch8762's avatar
falconpunch8762
Copper Contributor
Nov 14, 2021

Entering time duration in Minutes and seconds

hey all,

 

Ive done this before but I cant seem to remeber or even find the custom format to do it again. I am attempting to imput time duration into my spreedsheet. To be specific I am attempting to input plank times that range from 1 minute and 30 secs to a 4 minutes and 50 secs. In the past I was able to use a custom format that I had found online to just enter 130 and excel would convert it to 1:30 meaning m:ss in the spreedsheet can anyone help me do this again?

Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 14, 2021

    falconpunch8762 

    An alternative strategy might be to convert the 4-digit number to a time value.

    A traditional spreadsheet would require

     

    = (QUOTIENT($C3,100)+MOD($C3, 100)/60)/60/24

     

    whilst Excel 365 could be

     

    = LET(
  hrs,  QUOTIENT(+PseudoTimes,100),
  mins, MOD(PseudoTimes, 100)/60,
 (hrs+mins)/60/24)

     

    and 365 beta

     

    = Timeλ(PsudoTimes)

     

    where the Lambda function is defined by the second formula.

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 14, 2021

    falconpunch8762 A custom format like #":"#0 will display 130 to 1:30, but the underlying value will still be 130. So, it does not create a real duration from the number you enter. Format it as "Time" and you will get 00:00:00 on the 9th of May, 1900, being midnight on the 130th day in the Excel calendar.

Resources