time difference calcuation ?

[Tailgun]

I have a form that contains Date Opened Time Opened Date Closed and Time Closed.

The calculation for the diff in days between Date Opened and Date Closed works just fine including if the date is the same it returns 0 for days.

My problem is many times the Dates are O so I need to calculate the time diff so it can be saved to the DB.

For instance if Time Opened is 8:45:22 AM and Time Closed is 11:31:30 PM on the same day how do I calculate the hours? remembering that sometimes both the Open and Closed Times could be AM.

I know I need to start with an if Date Opened = Date Closed Then Time Diff = ??????

Thanks for any and all help. I'm hoping to get this solved by Monday since the boss is pressuring

 

[johnwm]

If Dateopened and DateClosed are both standard datetime format then try:

Msgbox Format ((DateClosed-DateOpened),"hh:mm:ss&quot
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Tailgun]

I wish they were but I can't at this point go back and change anything. I have seperate columns in the SQL database for each--ie DateOpened only contains the date and DateClosed only contains the date. TimeOpened only contains the time as in my original post. Same for TimeClosed.

 

[johnwm]

What format are they stored in? Are they stored as strings rather than dates?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[MichaelRed]

Coerce the Date/time combination to standard DateTime format vars.

Then use the 'built-in' MS functions. However I would recommend something more elaborate than the dimple subtraction / format given above, as it will give erroneous results for intervals greater than or equal to 24 hours.

You should review the intrinsic date functions in VB (DateAdd, DateDiff, DateSerial, TimeSerial. Use of these, after careful review of the arguments and their limitations should provide the complete soloution to your inquiry.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Tailgun]

Thanks MichaelRed I really appreciate the help

 

[johnwm]

Michael, Check the original question:
I know I need to start with an if Date Opened = Date Closed Then Time Diff = ??????

Tailgun,
Once you've checked DateOpened = DateClosed
Try:
Msgbox Format((TimeClosed-TimeOpened),"hh:mm:ss&quot
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[zgtrman]

I did something along these lines in college.
All the following code is in a class..it contains some code for another form but the essential time calculations are what you are after...I hope this helps in some way.

*********Start Class Code***********
Option Explicit

'Declare an Event with one parameter
Public Event OnMissingName(AClient As String)

Private Const conStrRunning As String = "Clock is running"
Private Const constrStopped As String = "Clock is stopped"


'Private instance variables
Private dtmStartTime As Date
Private dtmEndTime As Date
Private strClientName As String

'Keep a reference to the "Default" date/time
Private dtmInit As Date


'Minor deviation - elapsed time as date
Private dtmElapsedTime As Date
Private lngMinutes As Long
Private blnClockIsRunning As Boolean


'Start time is a read-only property
'Omit the property Let statement
Public Property Get StartTime() As Date
'Return value of dtmStart
StartTime = dtmStartTime
End Property

Public Property Get EndTime() As Date
'Return value of dtmStart
EndTime = dtmEndTime
End Property

Public Property Get ElapsedTime() As Date
Call CalcElapsedTime 'Calc Elapsed minutes
ElapsedTime = dtmElapsedTime
End Property

Public Property Get ElapsedMinutes() As Long
Call CalcElapsedTime 'Calc Elapsed minutes
ElapsedMinutes = lngMinutes
End Property

Public Property Get ClockIsRunning() As Date
ClockIsRunning = blnClockIsRunning
End Property

'Property write is basically a procedure with 1 parameter
Public Property Let ClientName(AClient As String)
On Error GoTo MissingClientErr
'Check what is passed in ...
If AClient <> &quot;&quot; Then
'Value OK - read the parameter into our instance variable
strClientName = AClient
Else
RaiseEvent OnMissingName(strClientName)


'Check again. If still blank raise an error
If strClientName = &quot;&quot; Then
'Stolen from page 313
Err.Raise vbObjectError + 513, &quot;CTime&quot;, &quot;Client name is blank.&quot;
End If
End If
Exit Property

MissingClientErr:
'Put a default Client Name
strClientName = &quot;MS Software&quot;

End Property
Public Property Get ClientName() As String
'Return whatever is in our instance variable
ClientName = strClientName
End Property


Public Property Get StatusMessage() As String
'A Property Get doesn't have to return a variable
If blnClockIsRunning Then
StatusMessage = conStrRunning 'return a const value
Else
StatusMessage = constrStopped
End If
End Property

Public Sub StartClock()
'Grab the system date and time
dtmStartTime = Now

'Reset our end time to the &quot;Default&quot; date/time
dtmEndTime = dtmInit

blnClockIsRunning = True

End Sub


Public Sub StopClock()
dtmEndTime = Now
blnClockIsRunning = False
End Sub


Private Sub CalcElapsedTime()
If Not blnClockIsRunning And dtmEndTime <> dtmInit Then
'User started and stopped the clock
dtmElapsedTime = dtmEndTime - dtmStartTime
'Calc Number of minutes
lngMinutes = DateDiff(&quot;s&quot;, dtmStartTime, dtmEndTime) \ 60
ElseIf blnClockIsRunning And dtmEndTime = dtmInit Then
'User hit start but never hit stop
dtmElapsedTime = Now - dtmStartTime
lngMinutes = DateDiff(&quot;s&quot;, dtmStartTime, Now) \ 60
Else
'User never did a start or a stop
dtmElapsedTime = dtmInit
lngMinutes = 0
'Good place to raise an event *************
End If

End Sub

*************End Class Code************************

zgtrman

 

[CCLINT]

>Coerce the Date/time combination to standard DateTime format vars.

This seems to be the best method.


I only want to somewhat explain and show on how to use the functions because of mis-understandings and the problems/difficulty involved if a calculation like this using VBA functions such as DateDiff().

This will give you a start time:
StartDateTime = DateValue (TheDate) + TimeValue(TheTime)

The end time is done the same way.

This will return the (total)hours passed:
Use the DateDiff function using an interval of seconds and divide the result by 3600:

?DateDiff(&quot;s&quot;, EndDateTime, StartDateTime) / 3600

and will give you a fraction of an hour as well.

Use:
?Int(DateDiff(&quot;s&quot;, StartDateTime, EndDateTime) / 3600)

in order to get just whole hours.

This will return the whole days passed:
DaysDiff=Int(DateDiff(&quot;s&quot;, StartDateTime, EndDateTime) / 86400)

So, putting it all together:

Public Function DaysAndTimeElapsed_pFstr(StartDateTime As Date, EndDateTime As Date) As String
Dim DaysDiff As Single
Dim HoursDiff As Single

DaysDiff = Int(DateDiff(&quot;s&quot;, StartDateTime, EndDateTime) / 86400)
HoursDiff = Int(DateDiff(&quot;s&quot;, StartDateTime, EndDateTime - DaysDiff) / 3600)

DaysAndTimeElapsed_pFstr = DaysDiff & &quot;d &quot; & HoursDiff & &quot;h&quot;
End Function

(If you want fraction of hours to be displayed, then remove the Int() from the hours calculation in the last line)

Notice in the calculation of the hours the expression:
EndDateTime-DaysDiff
This is needed beccause we only want the hours passed which cannot be included in the whole days results.

You would call the function like this:
strDiff = DaysAndTimeElapsed_pFstr(TheEndDate + TheEndTime,TheStartDate + TheStartTime)

Please note that the function could also be written like this:

Public Function DaysAndTimeElapsed_pFstr(StartDateTime As Date, EndDateTime As Date) As String
Dim DaysDiff As Single
Dim HoursDiff As Single

DaysDiff = DateDiff(&quot;s&quot;, StartDateTime, EndDateTime) / 86400
HoursDiff = Int((DaysDiff - Int(DaysDiff)) * 24)
DaysDiff = Int(DaysDiff)

DaysAndTimeElapsed_pFstr = DaysDiff & &quot;d &quot; & HoursDiff & &quot;h&quot;
End Function

If the dates and time stored in/ retrieved from the db as strings, then add a conversion function:

strDiff = DaysAndTimeElapsed_pFstr(DateValue(TheEndDate) + TimeValue(TheEndTime),DateValue(TheStartDate) + TimeValue(TheStartTime))

Of, course, you could pass each individual value to the function and have the function coerce the Date and time, and allow the values to be passed (ByVal) as a string or date.

It all needs to be done this way, (or a very similar method), for maximum accuracy, depending on the logic used for determining when a day or an hour has elapsed. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

And yet another way with-out using the DayDiff function:

Public Function DaysAndTimeElapsed_pFstr(StartDateTime As Date, EndDateTime As Date) As String
Dim Days As Single
Dim Hours As Single

Days = Int(EndDateTime - StartDateTime)
Hours = Hour(EndDateTime - StartDateTime - Days)

DaysAndTimeElapsed_pFstr = Days & &quot;d &quot; & Hours & &quot;h&quot;

End Function
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}