trouble with subtracting elapsed times

[NDBC]

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

 

[Joel]

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).

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

 

[OssieMac]

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

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).

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

 

[NDBC]

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.

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).

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

 

[NDBC]

Something else that seems funny. I save the time in b6 using the following
code that is activated by a button click

Worksheets("Timing Sheet").Range("b6").Value = Now

Lets say I clicked the button at 21:35:24 on the 7 august (so just now). The
cell shows 7/8/2009 9:35:24 PM which seems fine but if I convert it to
general format it comes up as 40032.89958. Is this right. Maybe that's the
number of days since 1/1/1900.

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.

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).

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

 

[OssieMac]

To NDBC,

As an added tip when working with times that can go over 24 hours, if you
declare date variables (In VBA times use Date variables because there are no
time variables) and all of your times are assigned to the date variables and
they include the date. for instance myTime = Now. myTime includes both Date
and time.

All calculations are then done with the VBA date variables because the date
variable holds both the date and time. That allows you to subtract a time
today from a time tomorrow and get the correct answer which if viewed as date
and time could actually be something like 1/1/1900 10:19:23. Convert this to
a serial number and it will be something like 1.43012731481.

Now if you format that number to a time using the worksheetfunction.text
using the [hh] option then it will display as 34:19:23.

You only convert to the time numberformat with worksheetfunction.text when
assigning the variable to a textbox etc so that it only displays the time
without the date. Never try to then use the textbox value for any
calculations. Always use the original VBA date variables.

You might find that you need to declare the date variables in the
Declarations area before any subs and declare them as public. That way they
will retain their values during the current session and are available for use
in any sub or module.
Example:
Public myDate as Date

Hope this info helps.

 

[NDBC]

I also noticed that 12/31/1899 12:01:45 AM seems to be stored in the cell as
text. If i change the number format of the cell it makes no difference at all.

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.

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).

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

 

[OssieMac]

Further to my previous post, there is another problem with dates in VBA. If
using dates from the worksheet, assign them to a VBA date variable first then
perform the calculations.

Dim time1 As Date
Dim time2 As Date

time2 = Sheets("Timing Sheet").Range("B6")

time1 = Now() - time2

Reason is that VBA becomes confused between the m/d/y and d/m/y formats but
assigning worksheet dates to a VBA variable first seems to work fine.

There are a number of areas in VBA where date problems occur.

 

[NDBC]

Now for some as examples. This is my code


Dim time1 As Date
Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
time1 = Now - start

When i use debugger to pause the code the values shown are

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM (well i just realised it changes every time i
hold the ouse over it)
time1 = 31/12/1899 12:24:26 AM

There is no "" around the values, they come up exactly as shown. I can't
understand it. The date in b6 is before now and is a real date as it can be
represented by a general number format. How can they subtract and not be
after 1900.

 

[Joel]

I can't find you problem from your postings, biut I can explain why you are
getting different results

1)
We now know that you times are starting at zero. You don't have any date
information in the time when it displays 12/31/1899 12:01:45 AM. This
really means you went back almost one day. Jan 1, 1900 minus 1 plus 1 hour
and 45 seconds.

A time (not a date) is entered into excel "01:45:33" defaults to day one Jan
1, 1900.

2)
This is important in debugging the problem. You need to find out why you
have text instead of a numbe
----------------------------------------------------------------------------------
I also noticed that 12/31/1899 12:01:45 AM seems to be stored in the cell as
text. If i change the number format of the cell it makes no difference at all.

----------------------------------------------------------------------------------

3)
Lets say I clicked the button at 21:35:24 on the 7 august (so just now). The
cell shows 7/8/2009 9:35:24 PM which seems fine but if I convert it to
general format it comes up as 40032.89958. Is this right. Maybe that's the
number of days since 1/1/1900.


You are correct!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


will get you hours
89958 * 24 = 21.58992
or use a formula
hour(40032.89958) = 21

21 hours

to get minutes
..58992 * 60 = 35.3952
35 minute

or use formula
minute(40032.89958) = 35

To get seconds

..3952 * 60 = 23.712
23 seconds

or use formula
second(40032.89958) = 23

4) Now lets look at your code

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


You have to make sure that Time2.lap and LastLap either both contains DATE
and TIME or both have only Time

If you have Aug 2 9:00 AM - Aug 1 10:00 PM you will get 11 hours which will
equal 11/24. Actually this is equivalent to Jan 1 , 1900 at 11:00 AM, but
the date isn't important.

If you have just 9:00 AM - 10:00 PM you will get -11/24. A minus time which
excel will the is Dec 31, 1899. The date doesn't matter.

You may getr incrrect information if you combine dates and time together.

If my start timne is

Aug 1, 2009 at 9:00 AM and my end time is just a time 10:00 AM then this
will get weird results


Jan 1, 1900 10:00 AM - Aug 1, 2009 9:00 AM


5) if you are mixing dates and time here are two tricks

If you want to add 10 hours to todays date


Int(now) get you midnight of any date

you can add a time and date

Int(now) + 10/24 get you 10:00 AM

or you can use a function

Int(now) + TimeSerial(10, 0, 0)

or

Int(now) + TimeValue("10:00 AM")


6) going the opposite way

Remove the date from the time


now mod 1

I also noticed that 12/31/1899 12:01:45 AM seems to be stored in the cell as
text. If i change the number format of the cell it makes no difference at all.

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.

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).

:

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

 

[OssieMac]

Try the following example. I put a stop in it so that you can hover the
cursor over the variables and see what they return after viewing the 2
MsgBox's.

The correct values are being returned. What occurs is that because VBA does
not handle hours from 2400 and above, the full day is subtracted and only
leaves the fractional portion but it you use the worksheetfunction.text to
display it then it is correct.

I understand your confusion. I've been through it and it took me quite
sometime to come to grips with it. That is the reason that I said to work
with the VBA variables and only use the Text function to display the value
and do not try to return the value from a userform textbox and use it in
calculations.

Sub test()

Dim time1 As Date

Dim start As Date

'Date and time in Range("B6")
'is 06 Aug 2009 09:35:24 PM

start = Sheets("Timing Sheet").Range("B6")

time1 = Now() - start

MsgBox WorksheetFunction.Text(time1, "[hh]:mm:ss")

MsgBox WorksheetFunction.Text(time1, "00.000000")

Stop

End Sub

 

[Joel]

You got exactly the results expected. but the results shouldn't of been
negative (before 1900). I think I know the problem

time1 = Now# - Start#

VBA is truncating the numbers. The pound will force excel to convert the
numbers to a double precision number. I also found that declaring the
variable asDATE create probelms. I some times use Double instead of Date.
When using DATE VBA is making an error in the caculation. What VBA is doing
is removing the date from the time leaving only the fraction of the dates.
Then subtracting and getting a negative number.

You can try this fix

Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date
Dim Timediff as Double

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
timediff = Now# - start#

Now for some as examples. This is my code


Dim time1 As Date
Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
time1 = Now - start

When i use debugger to pause the code the values shown are

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM (well i just realised it changes every time i
hold the ouse over it)
time1 = 31/12/1899 12:24:26 AM

There is no "" around the values, they come up exactly as shown. I can't
understand it. The date in b6 is before now and is a real date as it can be
represented by a general number format. How can they subtract and not be
after 1900.

To NDBC,

As an added tip when working with times that can go over 24 hours, if you
declare date variables (In VBA times use Date variables because there are no
time variables) and all of your times are assigned to the date variables and
they include the date. for instance myTime = Now. myTime includes both Date
and time.

All calculations are then done with the VBA date variables because the date
variable holds both the date and time. That allows you to subtract a time
today from a time tomorrow and get the correct answer which if viewed as date
and time could actually be something like 1/1/1900 10:19:23. Convert this to
a serial number and it will be something like 1.43012731481.

Now if you format that number to a time using the worksheetfunction.text
using the [hh] option then it will display as 34:19:23.

You only convert to the time numberformat with worksheetfunction.text when
assigning the variable to a textbox etc so that it only displays the time
without the date. Never try to then use the textbox value for any
calculations. Always use the original VBA date variables.

You might find that you need to declare the date variables in the
Declarations area before any subs and declare them as public. That way they
will retain their values during the current session and are available for use
in any sub or module.
Example:
Public myDate as Date

Hope this info helps.

 

[NDBC]

Joel, I'm using excel 2003. It doesn't like the # for some reason.

You got exactly the results expected. but the results shouldn't of been
negative (before 1900). I think I know the problem

time1 = Now# - Start#

VBA is truncating the numbers. The pound will force excel to convert the
numbers to a double precision number. I also found that declaring the
variable asDATE create probelms. I some times use Double instead of Date.
When using DATE VBA is making an error in the caculation. What VBA is doing
is removing the date from the time leaving only the fraction of the dates.
Then subtracting and getting a negative number.

You can try this fix

Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date
Dim Timediff as Double

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
timediff = Now# - start#

Now for some as examples. This is my code


Dim time1 As Date
Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
time1 = Now - start

When i use debugger to pause the code the values shown are

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM (well i just realised it changes every time i
hold the ouse over it)
time1 = 31/12/1899 12:24:26 AM

There is no "" around the values, they come up exactly as shown. I can't
understand it. The date in b6 is before now and is a real date as it can be
represented by a general number format. How can they subtract and not be
after 1900.

To NDBC,

As an added tip when working with times that can go over 24 hours, if you
declare date variables (In VBA times use Date variables because there are no
time variables) and all of your times are assigned to the date variables and
they include the date. for instance myTime = Now. myTime includes both Date
and time.

All calculations are then done with the VBA date variables because the date
variable holds both the date and time. That allows you to subtract a time
today from a time tomorrow and get the correct answer which if viewed as date
and time could actually be something like 1/1/1900 10:19:23. Convert this to
a serial number and it will be something like 1.43012731481.

Now if you format that number to a time using the worksheetfunction.text
using the [hh] option then it will display as 34:19:23.

You only convert to the time numberformat with worksheetfunction.text when
assigning the variable to a textbox etc so that it only displays the time
without the date. Never try to then use the textbox value for any
calculations. Always use the original VBA date variables.

You might find that you need to declare the date variables in the
Declarations area before any subs and declare them as public. That way they
will retain their values during the current session and are available for use
in any sub or module.
Example:
Public myDate as Date

Hope this info helps.

 

[Joel]

I just remebered that dates are treated in VBA as single not double.

So change
Dim Timediff as Double
to
Dim Timediff as Single

This wasn't causing the error. You can only use the # with numbers and not
variables. didn't realize that. so instead use the type conversion function
like below

time1 = CSng(Now()) - CSng(start)

I hate Excel !!!!!!!!!!!!!!! I hate Excel!!!!!!!!!!!!!!!!!! I hate
Excel!!!!!!!!!!!!!!!!!!!!!

There are so many things that just don't work properly and you must use
Kludges to get your code to work. Dates and times are one of these problems.
I'm an expert in working with excel dates and times and sometimes it takes
me hours to get this type of code to get the correct answers playing games by
converting to time to variables to numbers so the math works properly. Good
luck.

Joel, I'm using excel 2003. It doesn't like the # for some reason.

You got exactly the results expected. but the results shouldn't of been
negative (before 1900). I think I know the problem

time1 = Now# - Start#

VBA is truncating the numbers. The pound will force excel to convert the
numbers to a double precision number. I also found that declaring the
variable asDATE create probelms. I some times use Double instead of Date.
When using DATE VBA is making an error in the caculation. What VBA is doing
is removing the date from the time leaving only the fraction of the dates.
Then subtracting and getting a negative number.

You can try this fix

Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date
Dim Timediff as Double

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
timediff = Now# - start#

Now for some as examples. This is my code


Dim time1 As Date
Dim time2 As Date
Dim time3 As Date
Dim time4 As Date
Dim time5 As Date
Dim start As Date

'Stores rider numbers time when enter pressed
If KeyCode <> 13 Then
Exit Sub
End If
start = Sheets("Timing Sheet").Range("B6")
time1 = Now - start

When i use debugger to pause the code the values shown are

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM (well i just realised it changes every time i
hold the ouse over it)
time1 = 31/12/1899 12:24:26 AM

There is no "" around the values, they come up exactly as shown. I can't
understand it. The date in b6 is before now and is a real date as it can be
represented by a general number format. How can they subtract and not be
after 1900.



:

To NDBC,

As an added tip when working with times that can go over 24 hours, if you
declare date variables (In VBA times use Date variables because there are no
time variables) and all of your times are assigned to the date variables and
they include the date. for instance myTime = Now. myTime includes both Date
and time.

All calculations are then done with the VBA date variables because the date
variable holds both the date and time. That allows you to subtract a time
today from a time tomorrow and get the correct answer which if viewed as date
and time could actually be something like 1/1/1900 10:19:23. Convert this to
a serial number and it will be something like 1.43012731481.

Now if you format that number to a time using the worksheetfunction.text
using the [hh] option then it will display as 34:19:23.

You only convert to the time numberformat with worksheetfunction.text when
assigning the variable to a textbox etc so that it only displays the time
without the date. Never try to then use the textbox value for any
calculations. Always use the original VBA date variables.

You might find that you need to declare the date variables in the
Declarations area before any subs and declare them as public. That way they
will retain their values during the current session and are available for use
in any sub or module.
Example:
Public myDate as Date

Hope this info helps.

 

[OssieMac]

Joel's answer does not work in xl2007 or xl2002 either. Returns following
error.

Complile error:
Type-declaration character does not match decalred data.

Did you run the test that I posted? The problem is all with VBA's display of
the date/time. The correct underlying value is there. Trust me!!!!! (Just for
the record all code and answers that I post are tested unless I specifically
say it is untested.)

Modifying Joel's answer, the following will work and return the correct
double precision number.

Sub test()

Dim time1 As Date

Dim start As Date

Dim timeDiff As Double

Dim time1Double As Double

'Date and time in Range("B6")
'is 06 Aug 2009 09:35:24 PM

start = Sheets("Timing Sheet").Range("B6")

time1 = Now() - start

time1Double = time1

timeDiff = Now - start

MsgBox WorksheetFunction.Text(time1, "[hh]:mm:ss")

MsgBox WorksheetFunction.Text(time1, "00.000000")

Stop

End Sub

 

[Joel]

Ho wdo you have time1 declared. It must be a single. the type conversion
has alway worked for me before. Delcaring variables as dates is the cause of
the problem because VBA/Excel makes errors in the calculation with negative
time. It is not a display time, it is a mathematical error. To get the date
of 1899 means excel came up with a negative number when it should of been
positive.

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM

"now - start" should be postive and give a date after midnight Jan 1, 1900.
You are getting a result before midnight which means VBA got a negative
result. It is not a display problem. It is wrong math.

 

[NDBC]

OssieMac,

Thank you very much. I'm pretty sure I've got my head around it now. The
message boxes returned the values I expected. (eg 32:12:13 and 1.3567, these
are not a pair just example of magnitude)

I was also confusing the issue between textbox and variables. I previously
thought i had to put the times in text boxes to store them (even though the
text boxes weren't shown on my form). I now realise that really they are just
variables in time (sorry date) format and as long as they are made public
then I can use them in various subs.

All good now. Just have to go and make the rest of the changes.

Thank you so much. This has been annoying me for a long time.

 

[NDBC]

All's well men, I posted thanks but it turned up in the middle of all your
posts and you may have missed it.

Thanks for all your help. I wasn't expecting you both to be still posting
this morning. It was too late for me to keep going last night.

I still don't fully understand why subtracting two dates takes you back
before 1900 but I believe the numbers give you the right answer if shown in
the right format.

Ho wdo you have time1 declared. It must be a single. the type conversion
has alway worked for me before. Delcaring variables as dates is the cause of
the problem because VBA/Excel makes errors in the calculation with negative
time. It is not a display time, it is a mathematical error. To get the date
of 1899 means excel came up with a negative number when it should of been
positive.

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM

"now - start" should be postive and give a date after midnight Jan 1, 1900.
You are getting a result before midnight which means VBA got a negative
result. It is not a display problem. It is wrong math.

Joel's answer does not work in xl2007 or xl2002 either. Returns following
error.

Complile error:
Type-declaration character does not match decalred data.

Did you run the test that I posted? The problem is all with VBA's display of
the date/time. The correct underlying value is there. Trust me!!!!! (Just for
the record all code and answers that I post are tested unless I specifically
say it is untested.)

Modifying Joel's answer, the following will work and return the correct
double precision number.

Sub test()

Dim time1 As Date

Dim start As Date

Dim timeDiff As Double

Dim time1Double As Double

'Date and time in Range("B6")
'is 06 Aug 2009 09:35:24 PM

start = Sheets("Timing Sheet").Range("B6")

time1 = Now() - start

time1Double = time1

timeDiff = Now - start

MsgBox WorksheetFunction.Text(time1, "[hh]:mm:ss")

MsgBox WorksheetFunction.Text(time1, "00.000000")

Stop

End Sub