Time Formatting Problem

[Shawn French]

In the above table, I am having problems formatting cells F6 and G6 to display time as MM:SS. Obviously, there is a format option such as this, but due to F6 and G6 being calculated from cells C6, D6, and E6, the format of MM:SS shows up as zero's or a large number.

Here is the formula in cell F6:

=(((C6+D6)*(VLOOKUP(B6,$C$65:$D$100,2,FALSE))*E6)+(E6-1)*30)/60

Here is the formula for cell G6:

=25-F6

Any thoughts? Thank you all!

[#2]

Hi Shawn,

If 21.7 in F6 means twenty-one and some minutes when you shall add to your formula division on 24 and 60 and apply mm:ss format

Otherwise decimal 21.7 is considered as twenty-one and some days, applying [mm]:ss format returns 31248:00 minutes.

[#3]

Awesome, worked perfectly.

Now, how can I create a formula to get cell H6 to display the remaining time on a 25:00 time limit? In this case, the remaining time would be 3:18 which I want displayed in cell H7. I am having trouble using the same formats to display a simple subtraction equation.

Thank you Sergei!

[#4]

25 minutes that's 25/24/60 as a number. Thus you may use

=25/24/60-G6

and apply [mm]:ss format to show results in minutes.

Please note above doesn't work for the negative time difference. If such option is possible use one of these workarounds https://excelribbon.tips.net/T006239_Displaying_Negative_Times.html

[#5]

Sergei- YOU'RE A GENIUS. Thank you very much.

[#6]

You are welcome