Forum Discussion

SteveMorley's avatar
SteveMorley
Brass Contributor
Apr 05, 2019
Solved

Help with minutes/seconds to seconds.

Hi Excellers, I have some time fields Time 1- Time 4, as pictured below.  Without changing the format of the cells, what would be the formula to covert Time 1 of 1:35 to 95 seconds, and Time 3 of 1:...
excel
formula
  • Haytham Amairah's avatar
    Haytham Amairah
    Apr 05, 2019

    Hi Steve,

     

    The solution depends on the time format.

    I think that Excel read the time as hour:minute as shown in the below screenshot:

     

     

    If the time format that shown in the cell is hour:minute as the above screenshot, you need this formula:

    =TEXT((HOUR(A1)*60)+MINUTE(A1),"0")

     

    But, if the format is really minute:second, you need to use this formula instead:

    =TEXT((MINUTE(A1)*60)+SECOND(A1),"0")

     

    However, you have to make sure that you read the format as Excel read it.

    You may think that the format is minute:second while Excel read it as hour:minute.

     

    Hope that helps

     

    SteveMorley 

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Apr 05, 2019

Hi Steve,

 

The solution depends on the time format.

I think that Excel read the time as hour:minute as shown in the below screenshot:

 

 

If the time format that shown in the cell is hour:minute as the above screenshot, you need this formula:

=TEXT((HOUR(A1)*60)+MINUTE(A1),"0")

 

But, if the format is really minute:second, you need to use this formula instead:

=TEXT((MINUTE(A1)*60)+SECOND(A1),"0")

 

However, you have to make sure that you read the format as Excel read it.

You may think that the format is minute:second while Excel read it as hour:minute.

 

Hope that helps

 

SteveMorley 

Resources