Thanks Joel. When I use

Time1.Value = Now - Sheets("Timing Sheet").Range("B6")

I get a time of 12/31/1899 12:01:45 AM stored in the cell at an elapsed time
of 24:01:45. Excel then can not work with numbers before 1900. I am not even
sure how it can come with this number. The time stored in b6 is
6/08/2009 9:18:56 PM (as in 6 August, I'm an aussie). Any ideas what's
happening.



"Joel" wrote:

First You don't need the workshet function to perform this

Time1.Value = WorksheetFunction.Text(Now - Sheets("Timing
Sheet").Range("B6"), "[hh]:mm:ss")

use

Time1.Value = Format(Now - Sheets("Timing Sheet").Range("B6"), "[hh]:mm:ss")

This line is producting TEXT which is the problem.


Replace with this

Time1.Value = Now - Sheets("Timing Sheet").Range("B6")

Now is producing time from Jan 1, 1900 which will be larger the 24 hours (or
larger than 1)

To get at time less than 24 hours use mod function

Time1.Value = (Now mod 1) - Sheets("Timing Sheet").Range("B6")

Excel uses a number (not text) to store time and just changes the formaing
when displaying the time one the screen.

Jan 1, 1900 is day 1 and each day incremetns by 1. An hour is equivalent to
1/24 and a minute is equivalent to 1/(24 * 60) and a second 1/(24 * 60 * 60).

"NDBC" wrote:

I am inputing an elapsed time with the following code and it is working fine,

Time1.Value = WorksheetFunction.Text(Now - Sheets("Timing
Sheet").Range("B6"), "[hh]:mm:ss")

The problem occurs when I go to put the next elapsed time in the adjacent
cell. I have an if statement that checks if the lap time is within 20% of the
teams average.

If Time2.Value - LastLap 1.2 * Range("d" & riderCell.Row) Or Time2.Value
- LastLap < 0.8 * Range("d" & riderCell.Row) Then

When I go through debugger the lastlap time is working fine and is general
format, so when over 24hrs is something like 1.00347222. The time2.value
shows as "24:10:23" which is the actual elapsed time since the start of the
race to when they finish the lap. For some reason the two don't want to work
together.

I tried timevalue(time2.value) but this doesn't work if the elapsed time is
24hrs.

Thanks