Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "NDBC" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "NDBC" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "OssieMac" wrote: 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. -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 number ---------------------------------------------------------------------------------- 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 "NDBC" wrote: 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. "NDBC" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 -- Regards, OssieMac |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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# "NDBC" wrote: 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. "OssieMac" wrote: 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. -- Regards, OssieMac |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
Joel, I'm using excel 2003. It doesn't like the # for some reason.
"Joel" wrote: 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# "NDBC" wrote: 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. "OssieMac" wrote: 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. -- Regards, OssieMac |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "NDBC" wrote: Joel, I'm using excel 2003. It doesn't like the # for some reason. "Joel" wrote: 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# "NDBC" wrote: 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. "OssieMac" wrote: 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. -- Regards, OssieMac |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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 -- Regards, OssieMac |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "OssieMac" wrote: 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 -- Regards, OssieMac |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "OssieMac" wrote: 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 -- Regards, OssieMac |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
trouble with subtracting elapsed times
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. "Joel" wrote: 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. "OssieMac" wrote: 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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Elapsed times 24hrs | Excel Programming | |||
How do I find elapsed time between 2 dates and times? | Excel Worksheet Functions | |||
Accumulating elapsed times. | Excel Discussion (Misc queries) | |||
Pivot Table for 3 columns of elapsed times | Excel Discussion (Misc queries) | |||
Help with calculating elapsed times and formulas | Excel Programming |