SOLVED
Home

Help with minutes/seconds to seconds.

%3CLINGO-SUB%20id%3D%22lingo-sub-400877%22%20slang%3D%22en-US%22%3EHelp%20with%20minutes%2Fseconds%20to%20seconds.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excellers%2C%3C%2FP%3E%3CP%3EI%20have%20some%20time%20fields%20Time%201-%20Time%204%2C%20as%20pictured%20below.%26nbsp%3B%20Without%20changing%20the%20format%20of%20the%20cells%2C%20what%20would%20be%20the%20formula%20to%20covert%20Time%201%20of%201%3A35%20to%2095%20seconds%2C%20and%20Time%203%20of%201%3A29%20to%2089%20seconds%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20454px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F105919iDBBAA0284857DDB6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22average.jpg%22%20title%3D%22average.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-400877%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-400917%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20minutes%2Fseconds%20to%20seconds.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400917%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steve%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20depends%20on%20the%26nbsp%3Btime%20format.%3C%2FP%3E%3CP%3EI%20think%20that%20Excel%20read%20the%20time%20as%20hour%3Aminute%20as%20shown%20in%20the%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20717px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F105947iED2B5EDC50D5BB36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Time%20Format.png%22%20title%3D%22Time%20Format.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20time%20format%20that%20shown%20in%20the%20cell%20is%20%3CSTRONG%3Ehour%3Aminute%26nbsp%3B%3C%2FSTRONG%3Eas%20the%20above%20screenshot%2C%20you%20need%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DTEXT((HOUR(A1)*60)%2BMINUTE(A1)%2C%220%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20if%20the%20format%20is%20really%20minute%3Asecond%2C%20you%20need%20to%20use%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DTEXT((MINUTE(A1)*60)%2BSECOND(A1)%2C%220%22)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20720px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F105950iBFE7B95F76005D19%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Time%20Format%202.png%22%20title%3D%22Time%20Format%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20you%20have%20to%20make%20sure%20that%20you%20read%20the%20format%20as%20Excel%20read%20it.%3C%2FP%3E%3CP%3EYou%20may%20think%20that%20the%20format%20is%20minute%3Asecond%20while%20Excel%20read%20it%20as%20hour%3Aminute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313384%22%20target%3D%22_blank%22%3E%40SteveMorley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
SteveMorley
Occasional Contributor

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:29 to 89 seconds?

average.jpg

1 Reply
Solution

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:

 

Time Format.png

 

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

Time Format 2.png

 

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