Sep 13 2018 05:58 AM
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!
Sep 13 2018 06:40 AM
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.
Sep 13 2018 07:05 AM
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!
Sep 13 2018 07:18 AM
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