To Joel,

The VBA format function does not support [hh] but the worksheet.text
function does.

To NDBC,
Can you provide some examples of actual values for the variables. I suspect
that you might be getting negative dates/times and Excel does not support
negative times. A workaround is to convert the negative times to serial
numbers and then perform the comparisons.

--
Regards,

OssieMac


"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