Time conversion formula

[jimjam562]

In my line of work, I manually calculate events that finish and start using a calculator. I'm trying to create an excel spreadsheet to do these calculations for me, but I ran into a problem.

EX:

Current time: 10:00am

Total litres in order= 10000 litres

Flow rate= 5600 litres/hr

Time to finish= 1.79hrs, which converts to 1hr 47min.

Event Completed= 11:47am

My dilemma is getting the 1.79 to convert and add itself to the current time.

How can i get "Time to finish" to add to "Current time" through formulas. I'd like to just input numbers for "Total litres in order" and "Flow rate" and have everything else calculated. Thank you.

[#2 · Accepted solution]

@jimjam562 

For example:

Select A2 and press Ctrl+Shift+; to enter the current time (we cannot use a formula such as =NOW() for that would be updated each time Excel recalculates).

Enter total litres in B2.

Enter flow rate in C2.

Enter the following formula in D2:

=IFERROR(A2+B2/C2/24, "")

Format D2 as time.

The formula can be filled down.

[#3]

Thank you so much! One more question. I was hoping this formula would work for another equation, but it's not jiving. Say I want 'Total Litres' to be subtracted with 'Current Litres Delivered' then be divided by 'Flow Rate'. How would that change the equation so I can still get a completed time in relation to the current time. For some reason, it is subtracting from the current time instead of adding when I entered =IFERROR(A2+B2-C2/D2/24, "")

[#4]

@jimjam562 

It should be

=IFERROR(A2+(B2-C2)/D2/24, "")