How can I find the date and hour Difference???? 1

[Tulip]

Help needed please!! I need to run a report that will calculate the difference between when an order is placed and when an order is shipped. All I can seem to calculate is the day difference. Is there any way to calculate the hour difference as well.

Thanks in advance,

Desperate Access User !

 

[lameid]

Look up the dateDiff function in help. Using "h" as the interval gives you hours. Using the mod operator you can get hours past days... I.e. (TotalHoursResult Mod 24)

 

[Tulip]

Thanks so much Iameid, that is great but as always I now have another question!! Is there any way that I can tell it to exclude times outside business hours as at the moment a production cycle that is really only say 2 hours can appear as 15 hours and it is throwing off our metrics!

 

[lameid]

Easiest thing to do is to back out the number of hours between business days for the number of business days and then 24 hours for each non-business day. This is no simple matter but below is a function I wrote some time ago that calculates the number of weekdays between two dates. Note the third parameter countfirstday (True: monday to monday is 8 days or false: 7). I hope this gets you moving in the right direction.

Function weekDayDiff(StartDate As Date, EndDate As Date, CountFirstDay As Boolean) As Long
Dim dtStartAdj
Dim dtEndAdj
Dim lngWeeks As Long
Dim lngStartDay As Long
Dim lngEndDay As Long

lngStartDay = WeekDay(StartDate, vbSunday)
lngEndDay = WeekDay(EndDate, vbSunday)

dtStartAdj = StartDate - (lngStartDay - vbSunday)
dtEndAdj = EndDate - (lngEndDay - vbSunday)
lngWeeks = DateDiff("w", dtStartAdj, dtEndAdj, vbSunday, vbFirstJan1)

'comment Block1: (Friday to Monday is 1 not a weekend day to Monday) Function acts weird without below
If lngStartDay = vbSunday Then
lngStartDay = lngStartDay + 1
End If

If lngEndDay = vbSaturday Then
lngEndDay = lngEndDay - 1 'Saturdays don't count
End If
'End Comment Block1
weekDayDiff = lngWeeks * 5 - lngStartDay + lngEndDay + Abs(CountFirstDay) '+ vbSunday -vbSunday
End Function

 

[Tulip]

Thanks Iameid, your a life saver!