Difference of Start Time and End Time?

[OceanBlue]

A fellow member advised me to start a new thread concerning this problem. I don't want the user to have control on entering times so I have:

Private Sub Form_BeforeInsert(Cancel As Integer)

'Fill in Start Time
Me.Starttime = Format(Now, "Short Time") '",hh:mm:ss AMPM"
'Fill in Date
Me.LogDate = Format(Now)

End Sub

Private Sub txtTotalWorked_AfterUpdate()
'Fill in End Time
Me.Endtime = Format(Now, "Short Time") 'hh:mm:ss AMPM
End Sub

I need to get log hours as a Numberso that I can get the sum of hours and convert it into days.

Private Sub Endtime_AfterUpdate()
[LogHours] = ([Endtime] + [Starttime]) * 24
End Sub

[loghours] = Format(DateDiff("n", Me.Starttime, Me.Endtime) / 60, "#0.0")

[LogHours] = CDate([Endtime]) - CDate([Starttime])

I get 0 for Log Hours for each one of those. Any suggestions?

 

[TheQuestioner]

The Access Date data type caters for both date and time together - so there is no need to seperate the two.

I would do something like this instead

'Start time (insert into Form_BeforeInsert)
Me.txtStartDateTime=now

...

'End time (insert into txtTotalWorked_AfterUpdate)
Me.txtEndDateTime=now

...

'To calculate log hours (as an integer)
intLogHours=Cint(DateDiff("h",txtStartDateTime,txtEndDateTime))

'To covert log hours into days just divide the whole lot by 24

 

[OceanBlue]

TheQuestioner:
Thank you for your post. I tried as you recommended but I'm still not getting the correct answer.

Here's what I get:

Start time: 8:35 AM
End time: 9:45 AM
Log hours: 1


Start time: 8:35 AM
End time: 8:45 AM
Log hours: 0


Start time: 8:14 AM
End time: 10:46 AM
Log hours: 2

 

[edsuk]

You are returning an integer (CInt) hence the results being 1, 0 and 2 respectively.

If you want these as decimals go back to using "n" as the setting (minutes) and divide by 60.

intLogHours=DateDiff("n",txtStartDateTime,txtEndDateTime)/60

HTH

Mark...

 

[OceanBlue]

I implemented this:

[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3)

And from the table design view had to format loghours field to text, and everything is calculating correctly now.

Thanks!!