Converting 7 day week into 5 day week accurately 1

[Nilo]

I'm tracking how long certain requests have remained untouched, but I only need to know how long it was open in business days.
This is what I'm doing now:

DaysOpen: Fix((Date()-[Receipt Date])/7*5)

The problem is that I can't have the fraction portion visible.

Ex. 28.64 (no good)

28 or 29 (good but possibly wrong)

Every attempt to remedy this problem only creates a different problem.
This is what I've tried:
In the field properties, I change the format to Fixed with no decimal places.
This method rounds the numbers so you can have two different dates with the same value.
If you have two different Receipt Dates you should have two different values in the DayOpen field.
Above is my second attempt which uses the Fix function.
The problem doesn't occur as much but it still happens.
If I format it properly before I even get to the calculation, is there a way to remove Saturdays and Sundays?

Any help will be greatly appreciated
Thanks in advance

 

[a9]

I have had the same problem in the past. My solution was the code below which is not elegant but seems to work. You will also need a table of Bank Holidays (Public Holidays). The code is which can be used in a field in a query as per:

DAYS IN QUEUE: WorkingDays([indate],Now())


Function WorkingDays(StDate As Date, EndDate As Date) As Double
Dim MyDb As Database, MyRst As Recordset, Wdays
On Error GoTo Err_WorkingDays
Dim dys As Integer, ff As Integer, dd As Integer, ee As Integer

dys = (DateDiff("w", StDate, EndDate, 2, 0))
dys = dys * 5 '
If dys > 5 Then
If (WeekDay(EndDate, 2) > WeekDay(StDate, 2)) Then
ff = (WeekDay(EndDate, 2) - WeekDay(StDate, 2))
ElseIf (WeekDay(EndDate, 2) < WeekDay(StDate, 2)) Then
ff = 1 + (WeekDay(StDate, 2) - WeekDay(EndDate, 2))
End If
Else
dd = (WeekDay(EndDate, 2))
ee = WeekDay(StDate, 2)
If ee > dd Then
ff = (dd + 5) - ee
Else
ff = (dd - ee)
End If
End If
Wdays = (Int((dys + ff) * 100)) / 100
Set MyDb = CurrentDb
Set MyRst = MyDb.OpenRecordset(&quot;BankHolidays&quot;, dbOpenDynaset)
With MyRst
.MoveFirst
Do Until .EOF
If !BankHoliday >= StDate And !BankHoliday <= EndDate Then
Wdays = Wdays - 1
End If
.MoveNext
Loop
.close
End With
Set MyDb = Nothing
WorkingDays = Wdays


Exit_WorkingDays:
Exit Function

Err_WorkingDays:
WorkingDays = 0
Resume Exit_WorkingDays
End Function

 

[SimonDavis]

The problem with the 28 or 29 question is that your software is rounding the value, so effectively after midday (or fraction .5) it goes to the next day.

I found various ways to solve this by tweaking the result using NOW / DAY / TODAY.

There's an FAQ in the office forum about it. You might find a shorter fix by sticking with your formula, and fixing the result.

 

[Newposter]

Here's the formula that I use to track aging in Business Days:

=IF($E2=&quot;&quot;,(NETWORKDAYS($B2,NOW(),PIVOT!$D$1:$D$30)-1),(NETWORKDAYS($B2,$E2,PIVOT!$D$1:$D$30))-1)

In this example, E2 is the cell that shows Date Complete (blank if job not done); B2 is the cell for Date Received; PIVOT!$D1:$D30 is a table on another worksheet that has all the holidays of the year, so as to exclude them from the count; and the function NETWORKDAYS excludes Saturdays and Sundays. For some reason I have to have the &quot;-1&quot; in there to get the day correct - probably to fix the round-up.
NOTE: You have to have the Analysis Toolpak Add-In installed for the formula to work, on every PC that views it.

 

[MichaelRed]

See faq181-261

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over