Time value result form numbers and times combinations calculations

[Khaldon]

I need help please creating a formula in cell D1 of the attached sample work sheet to end up with time result for multiplying the result of subtraction of B1-A1 by 60 minutes and divided the end result by C1. As the following example:
B1-A1*60 minutes/7000. The attached example should result with 1 hour and 30 minutes.

Cell A1 and B1 values should be multiplied by a 1000 each in the needed formula as they are really a short cut for 219500 and 230000 respectively in this example.
Cell C1 is value of flow of 7000 each 60 minutes.

[Leith Ross]

Hello khaldon,

Add this formula to cell D1 and then format it for time. If you will have answers greater than 24 hours then select a time format like 30:37:55.

=(((B1*1000)-(A1*1000))*60/7000)/1440

[Khaldon]

Thank you for your reply, I replaced the 7000 with C1 as below since the 7000 is just an example figure and your fine formula still works, I did select a time format hh:mm:ss, however I would like please to see hours and minutes result only, is there away please to round up the seconds result if more than thirty to a minute?

=(((B1*1000)-(A1*1000))*60/C1)/1440

[Leith Ross]

Hello khaldon,

When you format the cell, select Custom and look for the following format [h]:mm. If it isn't in the list then add it and apply it the cell.

[Khaldon]

Hello Leith

I did select [hh]:mm earlier when I formatted the cell to customized cell time, however I had 1:29 instead of the 1:30 I am looking for in this particular example, the end result with [hh]:mm:ss selected was 1:29:43, I am trying to round up the 43 seconds to a full minute.

[daddylonglegs]

You can use MROUND to round to the nearest minute - try this version

=MROUND((B1-A1)/C1/24*1000,"0:01")

[Khaldon]

That dose it fine, however when I synchronized my excel file with my iPhone app Document TO GO, unfortunately it turn out that the MROUND is not supported function by my iphone app!

Can you please replace the formula and end up with the same result using a supported function from the link below.

http://support.dataviz.com/support.srch?DocID=2310

Beast regards,

[daddylonglegs]

OK, you can use ROUND like this

=ROUND((B1-A1)/C1*60*1000,0)/1440

[Khaldon]

Perfect, thanks again.