I've been trying to work out an accurate way displaying time remaining.

At the moment I tend to use a basic implementation which looks at how long it took to complete the last cycle through a loop, and depending on the size of the precentage, a time is worked out.

This is accurate for that last step but not for the overall loop.

I was wondering if there was a better way to do this?

Thanks

Dear Camil

I am not sure if you are asking a question relating to VBA or just a simple time calculation.

I would like a bit of more information to understand what is it that you want to achieve.

I am going to give you a general understanding of how Excel calculates time and also some things you have to keep in mind when you are performing such calculations.

When you insert a time for purpouse of calculation Excel converts it into a decimal no. So although for you 00:00:00 might be Midnight with 0 seconds but for Excel it is simply 0.0 and 23:59:59 is .999999.

All the times are between .0 and .999999.

How to Work with Time Formulas
How to Calculate Elapsed Time


Please refer to the Excel file that I have uploaded for the following explanation.

Cell A2 and B2 have time entered in them.
In Cell C2 and D2 the formula is exactly the same i.e. =B2-A2. You can see that C2 is displaying the result in Decimal format which is general Format from the Format > Cells… dialog box. And D2 is the Custom format as h:mm format in the Format Cells… Dialog box.


There may be a case where the two times that you are calculating fall between midnight. In this case you’ll have to account for the 24 hour time difference. You simply do this by adding number 1 which represents 24 hour period.

Please refer to the 24 hour time diff sheet in the uploaded excel file for the following explanation.

A simple calculation B2-A2 in cell D2 will give you the ##### error in the cell.
By modifying the formula to =B2-A2+IF(A2>B2,1) will make Excel Realize that the two times are falling between two separate days as


How to Accumulate Hours and Minutes Greater Than 24 Hours
If you want to correctly display a time greater than 24 hours, you can use the 37:30:55 built-in format. If you want to use a custom format instead, you must enclose the hours parameter of the format in brackets, for example:
[h]:mm

I hope this helps.

May be some of the things that I mentioned might help you to rectify your problem./.

If this posting has helped in answering your query then I would request you to mark the posting as Resolved. If, however, it hasn’t and you need further clarification then please press the reply button and ask for further clarification with your specific question. In case you have a related question then please as the question as a separate posting.


Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003