microsoft excel training day courses near kent - Tea Timer

Resolved · Low Priority · Version Standard

Thu 10 Jan 2008, 11:43 Delegate Camil said...

Camil has attended:
Excel Intermediate course

Tea Timer

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

Fri 18 Jan 2008, 13:17 Trainer Rajeev said...

RE: Tea Timer

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.