Please Help on Time Calculation 1

[jen1701]

I have 3 fields in a form [PartInDateTime],[PartOutDateTime] and ActualTime(duration). eg.
1/20/2004 1:30:20, 1/24/2004 3:50:40. I want to calculate ActualTime: 1/24/2004 3:50:40 - 1/20/2004 1:30:20 =
4days 2:20:20. Please give some ideas how to do that.

Thanks in advance.

Jen

 

[cghoga]

Hi Jen.

The DateDiff function should do it.

Take a look at DateDiff. It's pretty self explanatory, but if you have any questions please post back.

Hope this helps you.

 

[jen1701]

Hi cghoga,

Thank you for the reply. I did use DateDiff function. But the results are in h, n, or s. How to convert 112:15 (h:min) into Days hours min sec format.

jen

 

[jimb0ne]

you could use datediff with different intervals. So for example, first use datediff with "d" as an interval to determine the number of days, then use datediff with "h" as the interval, multiply the number of days by 24 then subtract that number from the returned hours and you'd have the number of days and hours, the rest would be reasonably simple to figure out, minutes and seconds that is, just do it the same way.

 

[jen1701]

Hi jimb0ne,

Thank you for the reply. I will try your suggestion.

Jen

 

[jimb0ne]

hallo,
I tried to bugger around with this a bit, here is something I did, it kinda works, the minutes aren't right on tho. I just plugged in the values you gave above, it got days and hours right, but then said there were 140 minutes, with a little more tweaking I might have somethign which you could use. sorry for the lack of help.

private function FindDateDifference(FirstDate as date, seconddate as date) as string
Dim iDays As Integer
Dim iHours As Integer
Dim sngMinutes As Single
Dim sngSeconds As Single

iDays = DateDiff("d", FirstDate, SecondDate)
iHours = DateDiff("h", FirstDate, SecondDate)
iHours = iHours - (iDays * 24)
sngMinutes = DateDiff("n", FirstDate, SecondDate)
sngMinutes = sngMinutes - (iDays * 1440)
FindDateDifference = iDays & " days, " & iHours & " hours, " & sngMinutes & " minutes"
end function

 

[jimb0ne]

jen1701,
the following procedure will calculate exactly what you want, I have it returning as a string, but you could format it as above quite simply. Hopefully this is what you wanted.

Private Function FindDateDifference(FirstDate As Date, SecondDate As Date) As String
Dim iDays As Integer
Dim iHours As Integer
Dim sngMinutes As Single
Dim sngSeconds As Single

iDays = DateDiff("d", FirstDate, SecondDate)
iHours = DateDiff("h", FirstDate, SecondDate)
sngMinutes = DateDiff("n", FirstDate, SecondDate)
sngSeconds = DateDiff("s", FirstDate, SecondDate)
sngSeconds = sngSeconds - (sngMinutes * 60)
sngMinutes = sngMinutes - (iHours * 60)
iHours = iHours - (iDays * 24)

FindDateDifference = iDays & " days, " & iHours & " hours, " & sngMinutes & " minutes, " & sngSeconds & " seconds"

End Function

 

[jen1701]

Hi jimb0ne,

Thank you ever so much for the codes you provided to me. I was in a meeting so I did not reply quickly. I gave you a star for the valuable reply. Thank you again.

Jen