Excluding working days

[John]

I have 2 datetime values and I need to calculate the time between them, not
counting saturdays and sundays. The output format should be:

1. Number of days with one decimal: example 9,6 (numeric)
For this I currently use round(Now-DateTimeIn,1)

2. In days, hours, minutes, second: example 12:03:23:21 (string)
For this I currently use a function that uses:
interval = Now - DateTimeIn
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

I found several functions on the internet that calculate the fixed number of
working days. None of them has the output in the 2 formats as described
above.

Can someone give me a hint on how to achieve this?
Thanks in advance,
John

 

[John]

Oops

I found several functions on the internet that calculate the fixed number
of working days.

should be

I found several functions on the internet that calculate the fixed number of
days (without the decimal) between two datetime values, not counting
saturdays and sundays.

John

 

[Larry Linson]

Start by reading help on the DateDiff and DateAdd functions.

Larry Linson
Microsoft Office Access MVP

 

[John]

Thanks. I solved it with a function that simulates another enddate, just for
calculation purposes.

Public Function calcEndDate(dateStart As Date, dateEnd As Date) As Date
Dim iDays, iWorkDays, sDay, i As Integer

iDays = DateDiff("d", dateStart, dateEnd)
iWorkDays = 0
For i = 1 To iDays
sDay = Weekday(DateAdd("d", i, dateStart))
If sDay <> 1 And sDay <> 7 Then '1 is sunday
iWorkDays = iWorkDays + 1
End If
Next
If iWorkDays - iDays = 0 Then
calcEndDate = dateEnd
Else
calcEndDate = DateAdd("d", iWorkDays - iDays, dateEnd)
End If
End Function

John