Access Formula (DiffDate)

[ielashi]

Hello,
I was wondering how I can make Microsoft Access calculate the difference between dates. Where a column contains the time the employee entered in and another column containing the time the employee left. I would like to have in a decimal form (for example: if somone cameat 8:30 and left at 10) the calculated field would contain 1.5). Any help would be really appreciated. Thanks in advance! :)

[kfcSmitty]

MsgBox (DateDiff("n", Text0.Value, Text2.Value) / 60)


Will accomplish what you want..I tried using hours..but it wouldnt give me a decimal number :/

[Mark Gambo]

This may work a little better for you:


Private Sub Command1_Click()
Dim intMinutes As Integer
Dim intHours As Integer
Text0 = "8:30"
Text3 = "10:00"
intHours = 0
intMinutes = DateDiff("n", [Text0], [Text3])
intHours = intMinutes \ 60
intMinutes = intMinutes - (intHours * 60)

Text5 = intHours & ":" & Format(intMinutes, "00")
End Sub

[Mark Gambo]

I was reviewing you post again and I noticed that you wanted the result to be "1.5", so I adjusted my code to return both "1:30" and "1.50":

Private Sub Command1_Click()
Dim intMinutes As Integer
Dim intHours As Integer
Text0 = "8:30"
Text3 = "10:00"
intHours = 0
intMinutes = DateDiff("n", [Text0], [Text3])
intHours = intMinutes \ 60
intMinutes = intMinutes - (intHours * 60)

'The Following line will return '1:30'
Text5 = intHours & ":" & Format(intMinutes, "00")

'The Following line will return '1.50'
Text7 = intHours & "." & Format(100 / (60 / intMinutes), "00")
End Sub

[Ecniv]

An alternative:

cdbl(cdate("10:00")-cdate("08:30"))*24

Where the Cdates are you can use a field with the time in.
Note: This will probably only work for time differences, or date/times on the same date. Different dates would produce weird results :sick:

[Mark Gambo]

An alternative:

cdbl(cdate("10:00")-cdate("08:30"))*24

Where the Cdates are you can use a field with the time in.
Note: This will probably only work for time differences, or date/times on the same date. Different dates would produce weird results :sick:


Nice piece of code!!!