[RESOLVED] Some doubts about DateDifference - calculating time

Hi...:wave:

I need to use DateDiff() to calculate the difference in Time.

Code:

---

Private Sub Form_Load()
    Dim myDate1 As Date
    Dim myDate2 As Date
   
    Dim lngMin  As Long
   
    'mydate1 will hold another value
    myDate2 = Now
   
    lngMin = DateDiff("n", myDate1, myDate2)
    Me.Caption = lngMin

End Sub

---

Here, myDate1 will hold a date value stored some days before.(using Now() function).

Will there be any problems with this calculation. I heard that there's an issue with midnight time. What's it ? Can anybody explain it ?

Thanks in advance...:wave:

I am not aware of any midnight issue with DateDiff. However, midnight is an issue if using VB's Timer function, since the Timer value rolls over after midnight.

Akhil

Likewise, I'm not aware of a midnight issue with DateDiff.

Could you possibly be thinking of the midnight issue
assocaited with the Timer function?

It is sometimes used as a quickie way to establish
elapsed time, say in a long loop, as in:

Code:

---

beginTime = Timer
< long loop >
endTime = Timer
elapsedTime = endTime - beginTime

---

Normally, that code would be fine.

However, since Timer returns the number of seconds
from midnight, use of that code for an interval that
straddled midnight would give faulty results.

EDIT:

I'd say that the reason your code frag would NOT
suffer from the midnight issue is that your vars are both
-- set by the Now() function, and
-- actually hold a date and the time.

Spoo

Thanks guys....:wave:

While I was going through some posts, I read those midnight issue in a post. But I don't remember much.
That's why I asked. :)

Another question:
One of my old program uses the following code:

Code:

---

Private Sub Timer1_Timer()
    lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Time), "0:0:0"), "hh:mm:ss")
End Sub

---

Which calculates the time elapsed. Timer1 has an interval of 1000.
Start_Time is a string variable.

Code:

---

Start_Time = Time

---

Will this cause any issues ? Do you see any flaws in this one ?

Akhil

Yes, I see the possibility of a flaw with that approach.

To wit:

Code:

---

    Dim sTime As Date
    Dim eTime1 As Date
    Dim eTime2 As Date
    '
    sTime = #11:58:55 PM#
    eTime1 = #11:59:55 PM#
    eTime2 = #12:01:00 AM#
    '
    v1 = DateDiff("s", sTime, eTime1)    ' v1 = 60
    v2 = DateDiff("s", sTime, eTime2)    ' v2 = -86275

---

Does that constitute a flaw in your opinion?

EDIT:

The key here is the difference between Time and Now

Code:

---

    eTime1 = Time    ' 8:58:46 AM
    eTime2 = Now    ' 5/20/2010 8:58:46 AM

---

Now includes the date.
Time does not, and hence is susceptible to the midnight issue.

Spoo

Oh Yeah... ! Thanks for pointing that one... :wave:

I think, it can be solved, if we use Now(). Am I right ?

Akhil

I'd say yes.

Spoo

Thanks..:wave:

Another question:

Code:

---

Dim strTemp As String
    Dim tSec As Byte, tMin As Byte, tHour As Byte
   
    For i = LBound(myArray) To UBound(myArray)
        strTemp = Split(myArray(i), ":")
       
        tHour = tHour + Val(strTemp(0))
        tMin = tMin + Val(strTemp(1))
        tSec = tSec + Val(strTemp(2))
        If tSec >= 60 Then
            tMin = tMin + 1
            tSec = tSec - 60
        End If
       
        If tMin >= 60 Then
            tHour = tHour + 1
            tMin = tMin - 60
        End If
    Next
    lblTotalTime.Caption = CStr(tHour) & " hrs " & CStr(tMin) & " mins " & CStr(tSec) & " secs"

---

This is the code that I use for finding the total of all elapsed time, which's stored in a String Array. Do you have any ideas/suggestions on improving this one ?

Example data in the string array:

Code:

---

00:25:01
05:13:00
01:56:19
01:30:33

etc...

---

These elapsed time in this array was inserted from the data obtained using the code in post#4.

Akhil

That all seems fine to me.

I initially had the thought that you could move the
If tSec >= 60 and If tMin >= 60 outside the loop
and do the "updating" just once, but ..

- the performance improvement would be virtually nil
- it would require a more complex set of logic

.. so .. don't change anything !

Spoo

Thanks Spoo...:wave:

I have some other questions too. But right now, I'm somewhat busy. I'll post the rest of the questions soon..:wave:

Quote:

---

Originally Posted by akhileshbc

This is the code that I use for finding the total of all elapsed time, which's stored in a String Array. Do you have any ideas/suggestions on improving this one ?

Example data in the string array:
00:25:01
05:13:00
01:56:19
01:30:33

---

Suggestions? Consider using a Date array vs string array. Not only will you be able to use the DateDiff function more easily, you are actually saving memory too: Date variable = 8 bytes each; your 8-char string = 22 bytes each

Thanks for the info..:wave:

But they are previously calculated Elapsed times. In post#8, the code will first grab all previously calculated data from a file/database into a string array. And then, the total elapsed time is found.

Any ideas to simplify the process ?

I still think using date arrays could be much easier and possibly more accurate. Here's an example using your 4 time elapsed totals as a date vs string

Code:

---

Private Sub Command1_Click()

    Dim dtElapsed(0 To 3) As Date
    Dim dtTotal As Date, X As Long
   
    dtElapsed(0) = TimeSerial(0, 25, 1)
    dtElapsed(1) = TimeSerial(5, 13, 0)
    dtElapsed(2) = TimeSerial(1, 56, 19)
    dtElapsed(3) = TimeSerial(1, 30, 33)
   
    For X = 0 To UBound(dtElapsed)
        dtTotal = dtTotal + dtElapsed(X)
    Next
   
    Debug.Print "days: "; DateDiff("d", Date, Date + dtTotal);
    Debug.Print " hrs: "; Hour(dtTotal); " mins: "; Minute(dtTotal);
    Debug.Print " secs: "; Second(dtTotal)

End Sub

---

Reference previous reply. To get number of total days elapsed, another option:
Replace: Debug.Print "days: "; DateDiff("d", Date, Date + dtTotal);
With: Debug.Print "days: "; DateDiff("d", 0#, dtTotal);

Thanks..:wave:
I think your idea is to store the elapsed time in a date variable(for eg: datetime field in database). Right ? And then add these datetime records to find the total.

If I want to use DATE variables, what are the things necessary to change in this code:

Code:

---

lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Time), "0:0:0"), "hh:mm:ss")

---

Are these the only changes needed ?

Code:

---

Dim Start_Time As Date
Start_Time = Now

Private Sub Timer1_Timer()
    lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0"), "hh:mm:ss")
End Sub

---

Code:

---

lblElapsedTime.Caption = Format(DateDiff("s", Now(), Start_Time), "hh:nn:ss")

---

P.S. If dates are stored in database as datetime datatypes, then you very well may be able to return a SUM() query vs looping thru a recordset and adding them up manually.

Ah! got the idea...:wave:

I will do the updation and will post the result...:wave:

Thanks LaVolpe and Spoo...:wave:

I have another doubt:

I had tested this code:

Code:

---

Option Explicit
Dim Start_Time As Date

Private Sub Command1_Click()
    Me.Caption = DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0")  '~~~ Calculate the difference
End Sub

Private Sub Form_Load()
    Start_Time = Now    '~~~ Set the starting time
End Sub

---

and the result on clicking the button after 5 seconds is 12:00:05 AM
Is this what I have to store into the db ?
And why there's no date ?

After running the program, I have changed the date of the system from 21st May to 22nd May. The result obtained is: 12/31/1899 12:00:11 AM

I have tried your code in post#16. But I think, the format function is not getting it correctly. It shows only 00:00:00

Sorry for these dump questions..:wave:

Akhil

When you say...

Quote:

---

result on clicking the button after 5 seconds is 12:00:05 AM

---

... are you referring to Me.Caption, as in

Code:

---

Private Sub Command1_Click()
    Me.Caption = DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0") 
End Sub

---

If so, perhaps you could, for troubleshooting purposes, break apart
that line of code, as in:

Code:

---

Private Sub Command1_Click()
    v1 = DateDiff("s", Start_Time, Now)
    v2 = DateAdd("s", v1, "0:0:0") 
    v3 = Now
    v4 = DateAdd("s", v1, v3)
End Sub

---

If I understand what is happening, you'd get these results:

1. v1 = 5 .... calculates the difference
2. v2 = 12:00:05 AM .... adds said difference to a preset time, namely, "0:0:0"
3. v3 = 5/21/2010 9:22:41 AM
4. v4 = 5/21/2010 9:22:46 AM

Comments:

1. No issue here
2. The "problem" here is you are doing trying to add 5 seconds to "0:0:0"
   (which is being interpreted as 12:00:00 AM). This seems to be the
   where the breakdown in your logic flow is occurring.
3. Hypothetical value of NOW
4. No DateAdd problem here, because you are adding 5 seconds to a time
   (which also contains the date).

Does that help?

Spoo

Quote:

---

Originally Posted by akhileshbc

I have another doubt:

I have tried your code in post#16. But I think, the format function is not getting it correctly. It shows only 00:00:00

Sorry for these dump questions..:wave:

---

Oops, I think this is more in line with your thoughts:

Code:

---

lblElapsedTime.Caption =  Format(TimeSerial(0, 0, DateDiff("s", Now(), Start_Time)), "hh:nn:ss")

---

Edited: You may want to use something similar to anhn's suggestion below. TimeSerial has a major limitation: the number of seconds passed to it must be an Integer, therefore, any time lapse greater than 32767 seconds (a little over 9 hours) will generate an overflow error.

Try this:

Code:

---

Private Sub Command1_Click()
    Dim dt As Date
   
    dt = Now() - Start_Time
    Me.Caption = CLng(Int(dt)) & "d " & Format(dt, "hh:nn:ss")
End Sub

---

Thanks guys..:wave:

But I'm a bit confused with the dateandtime !

I will explain my program's function:

Day1: Finds 5 elapsed times and store it to db.
Day2: Finds 3 elapsed times and store it to db.
Day3: Finds 2 elapsed times and store it to db.
Day4: Finds the total from 10 entries in db and displays it.

@Spoo: Thanks..:wave: So, 0:0:0 is considered as 12:00:00 AM ?

@LaVolpe: Thanks...:wave: That's working too. But how/what should be stored to the db as elapsed time ? (I'm a bit confused with the data types. In my MS Access db, do I have to store it in DateTime field ?) Do I need to remove the formatting, before saving it to db ?

@anhn: Thanks..:wave: When I checked the value of dt, it is similar to the result: 12:00:00 AM. If I store that dt value in my db (MS Access - DateandTime field), and then using the SUM() sql function, will it work correctly or any flaws ?

Quote:

---

Originally Posted by akhileshbc

I will explain my program's function:

Day1: Finds 5 elapsed times and store it to db.
Day2: Finds 3 elapsed times and store it to db.
Day3: Finds 2 elapsed times and store it to db.

Day4: Finds the total from 10 entries in db and displays it.

---

Akhil

Perhaps the easiest is to just store elapsed times (in seconds)
So, for example, if your DB looked like...

Code:

---

Rec  Day  NN    ET
 1    1  1    100
 2    1  2  2000
 3    1  3    100
 4    1  4    100
 5    1  5  2000
 6    2  1    100
 7    2  2    100
 8    2  3  3000
 9    3  1    300
10    3  2    20

---

Then, when needed (on Day4), just sum the ET's

Code:

---

totSecs = 7820  ' obtained by using a loop, or SQL statement

---

and do a 1-time conversion from seconds to hrs, mins, secs

Code:

---

intHRS = Int(totSecs / 3600)          '  2 hrs
remainSecs = totSec - intHRS * 3600
intMINS = Int(remainSecs / 60)        ' 10 mins
intSECS = remainSecs - intMINS * 60    ' 20 secs

---

Just a thought.
Spoo

Quote:

---

Originally Posted by akhileshbc

@anhn: Thanks..:wave: When I checked the value of dt, it is similar to the result: 12:00:00 AM. If I store that dt value in my db (MS Access - DateandTime field), and then using the SUM() sql function, will it work correctly or any flaws ?

---

Date data type is stored internally as a Double value (but much smaller range) with the integer part as number of days (from 1/1/1900?) and the decimal part is time value.
eg. 22/05/2010 12:44:03 AM is stored as 40320.0305902778
So you can add or subtract DateTime values the same way as with Double values.

Code:

---

Dim dt1 As Date
Dim dt2 As Date
Dim dt As Date

dt1 = #5/21/2010 9:12:25 PM#
dt2 = #5/22/2010 12:44:03 AM#
dt = dt2 - dt1
Debug.Print CLng(Int(dt)) & "d " & Format(dt, "hh:nn:ss")

---

Code:

---

0d 03:31:38

---

And to piggyback, I don't foresee any issues. Using 2 elapsed times of 0:5:0 & 0:15:10, stored in the database would look like this inside your db: 12:05:00 AM & 12:15:10 AM. Sum() should return value of 12:20:10 AM, but that value in a "hh:nn:ss" formatted string = 0:20:10, not 12:20:10. Additionally Hour() returns 0, Minute() returns 20: Second() returns 10.

@Spoo: That's nice. Thanks for the idea...:wave:
@anhn: Thanks for providing more details...:wave:
@LaVolpe: Thanks...:wave: But what happens if the elapsed time is more than a day (I mean 24+ hours). Will it work correctly ?

And what is the correct format of parameters in DateDiff() function ?
Is it like this: DateDiff("s", presentTime, previousTime)
or, like this: DateDiff("s", previousTime, presentTime)
...???

Thanks again guys...:wave:

Quote:

---

Originally Posted by akhileshbc

And what is the correct format of parameters in DateDiff() function ?

Is it like this: DateDiff("s", presentTime, previousTime)
or, like this: DateDiff("s", previousTime, presentTime)

---

Akhil

Short answer .. either way.

Long answer:

v1 = DateDiff("s", previousTime, presentTime) -- returns a positive number
v2 = DateDiff("s", presentTime, previousTime) -- returns a negative number

Spoo

Quote:

---

Originally Posted by Spoo

Akhil

Short answer .. either way.

Long answer:

v1 = DateDiff("s", previousTime, presentTime) -- returns a positive number
v2 = DateDiff("s", presentTime, previousTime) -- returns a negative number

Spoo

---

Thanks Spoo...:wave:

Quote:

---

Originally Posted by akhileshbc

@LaVolpe: Thanks...:wave: But what happens if the elapsed time is more than a day (I mean 24+ hours). Will it work correctly ?

---

Code:

---

DateDiff("d", 0#, CDate(SumOfElapsedTimes))

---

A query like Select Sum(Table1.ElapsedTimes) As SumOfElapsedTimes may return something like 0.01753472222, so you will want to use CDate as described above... and here:

Code:

---

Dim dt As Date
dt = CDate(SumOfElapsedTimes)
Debug.Print  DateDiff("d", 0#, dt) & "  " & Format(dt, "hh:nn:ss")

---

Why need to use DateDiff()?
Elapsed times can be declared as Double instead of Date.

Code:

---

Dim dt1 As Date
Dim dt2 As Date
Dim e1  As Double
Dim e2  As Double
Dim TotalElapsed As Double

dt1 = #5/21/2010 3:12:25 AM#
dt2 = #5/22/2010 7:44:53 AM#
e1 = dt2 - dt1
Debug.Print "e1 = " & Int(e1) & "d " & Format(e1, "hh:nn:ss")

dt1 = #5/22/2010 1:32:27 PM#
dt2 = #5/23/2010 10:12:05 AM#
e2 = dt2 - dt1
Debug.Print "e2 = " & Int(e2) & "d " & Format(e2, "hh:nn:ss")

TotalElapsed = e1 + e2
Debug.Print "TotalElapsed = " & Int(TotalElapsed) & "d " & Format(TotalElapsed, "hh:nn:ss")

Debug.Print "Days : " & Int(TotalElapsed)
Debug.Print "Hours: " & Hour(TotalElapsed)
Debug.Print "Mins : " & Minute(TotalElapsed)
Debug.Print "Secs : " & Second(TotalElapsed)

---

Code:

---

e1 = 1d 04:32:28
e2 = 0d 20:39:38
TotalElapsed = 2d 01:12:06
Days : 2
Hours: 1
Mins : 12
Secs : 6

---

Thanks guys...:wave:

Yeah, I will use the Double idea ....:wave:

Thank you so much...:wave: