Forum Discussion
Time conversion formula
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.
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 Replies
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.
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, "")
