Time Calculations

[DMT Dave]

Hi Guy's for the life of me !! this should be quite simple but can't see it



I am trying to calculate start and finish times and work out total hours

I have a field HoursDone set to number / StartTime set to short time / FinishTime set to Short Time

If a start time is 21:30 1 day ago

Finish time is 15:00

I need total hours to be 17.5

but start time is day before ??

also if start time is same day, this i guess will be as easy as DateDiff(FinishTime,StartTime) ??

I can't see it ?????

Dim myMins As LongDim myHrs As Double


myMins = Format(DateDiff("n", Me.StartTime, Me.FinishTime))
myHrs = myMins / 60
Me.HoursDone = Left(myHrs, 4)

[ranman256]

Be sure your fields save both DATE/TIME,
then elepased in hours,h, is: DateDiff(“h”,datefld1,dateFld2)

[DMT Dave]

Hi Ranman yes all set as suggested, i think my question is, the finish time can be either greater than or less than the start time ie: start time before midnight then, start time number is greater than finish time, starting after midnight then the start time number is less than the finish time, does it need calculating differently ie: if one is greater than the other etc....

This is what i have tried to adjust but going wrong somewhere

Dim myMins As DateDim myHrs As Long
Dim myTime1 As Date, myTime2 As Date


If Me.FinishTime < Me.StartTime Then
myTime1 = DateDiff("n", Me.FinishTime, "00:00")
myTime2 = DateDiff("n", "00:00", Me.FinishTime)
myMins = DateDiff("n", myTime1, myTime2)
myHrs = myMins / 60
Me.Mins = myMins
Me.HoursDone = myHrs
End If


If Me.FinishTime > Me.StartTime Then
myMins = DateDiff("n", Me.StartTime, Me.FinishTime)
myHrs = myMins / 60
Me.Mins = myMins
Me.HoursDone = myHrs
End If

Result is:



Which should be 22 Hrs

[ranman256]

why all this code? use the 1 line I gave you.
YOU MUST INCLUDE DATE in the calculation. Thats why the field holds both date and time.

[DMT Dave]

its still wrong though as below

myMins = DateDiff("n", Me.FinishTime, Me.StartTime)myHrs = myMins / 60
Me.Mins = myMins
Me.HoursDone = myMins / 60

Result is 2 hours / 120 minutes

Starting at 22:40 and finishing at 20:40 is 22 hours / 1320 minutes

So do I not need to verify if start time is is > than finish time ? and how would i do that ?

Kindest

[Welshgasman]

I tend to test in the immediate window?

tt=#07/03/2021 21:00#
? tt
03/07/2021 21:00:00 

? datediff("n",now(),tt)
-1065 
? 1065/60
 17.75 

? 1065 mod 60
 45

Run at 14:45 today

[orange]

Dave:

' ----------------------------------------------------------------
' Procedure Name: GetTimeDiff
' Purpose: Return the number of hours between 2 dates
' Procedure Kind: Function
' Procedure Access: Public
' Parameter dateStart (Date): period startdate with time
' Parameter dateEnd (Date):  period enddate with time
' Return Type: Single
' Author: Jack
' Date: 04-Jul-21
' ----------------------------------------------------------------
Function GetTimeDiff(dateStart As Date, dateEnd As Date) As Single
    'dateStart and dateEnd should be Date with Time
    Dim datehold As Date ' work area if datestart >DateEnd
    If dateStart > dateEnd Then 'swap the dates
        datehold = dateStart
        dateStart = dateEnd
        dateEnd = datehold
    End If
    GetTimeDiff = DateDiff("s", dateStart, dateEnd) / 60 / 60 'convert seconds to minutes to hours
End Function

Test routine

Sub testgettimediff()
Dim date_start As Date
Dim date_end As Date
date_start = #7/3/2021 9:30:00 PM#
date_end = #7/4/2021 3:00:00 PM#
Debug.Print GetTimeDiff(date_start, date_end)
End Sub

Result:

17.5

[CJ_London]

think Dave has not got the fact you need to include the Date as part of the calculation.

If end time cannot be more than the start time (i.e. total time greater than 24 hours) you can do a simple test to check and adjust your calculation


?round((abs(#22:00#<=#21:30#)+#22:00#-#21:30#)*24,2)
0.5
?round((abs(#15:00#<=#21:30#)+#15:00#-#21:30#)*24,2)
17.5
?round((abs(#22:00#<=#22:00#)+#22:00#-#22:00#)*24,2)
24

or using Dave's field names

round((abs(FinishTime<=StartTime)+FinishTime-StartTime)*24,2)

[DMT Dave]

Thank you guy's will test

all appreciated