Networkdays

[GORDON]

I am having a problem with this formula in that it gives me 2 extra day
sometimes and other times it works ou ok. Can anyone help me figure ou
what I am missing. See my attachment for formula and sample data.
Thanks

+-------------------------------------------------------------------
|Filename: Image1.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4020
+-------------------------------------------------------------------

 

[Roger Govier]

Hi Gordon

Try
=IF(A3="","",NETWORKDAYS(A3,IF(COUNT(B323),MIN(B33),$F$2)))
I get results of
1, ,1,4,10,102,4,57,1022,6 as opposed to you thinking it should be
1, ,1,4,10,106,54,57,1022,6

whereas your formula is returning
1, ,3,6,12,108,55,57,1024,8

I suspect that your expected results for lines 4,8 and 9 may be incorrect.

Regards

Roger Govier

 

[GORDON]

Hi Roger,

Tried your formula and the results I get is still errors in not
calculating correctly. For instance as a test I used the dates:
Date In ........2005-09-15
Date Out .....2005-09-20
Letter Out....2005-09-28
Completed...2005-10-28
The expected answer should be 27 days from start to finish. but I get 4
days as a result of your formula. Is there something that I am still
missing. Instead of the range that you suggested, I just used B33
but that didn't make any difference. I am using excel 2002 with the
addins. Is there something wrong with my calculations using this older
version of excel.

 

[Roger Govier]

Hi Gordon

From the .gif you posted, I could only guess at what you were trying to
achieve, but the formula I gave seemed to achieve your expected result in
most cases, other than 2, where I suspected that your expectation was incorrect.

On some lines in your example, there were only dates in Date Out, in other
cases there were only dates in completed. I assumed you wanted to compare
the date in with the EARLIEST of Date Out, Letter Out, Completed or, in the
absence of any of these, with the date held in cell F2.

If you are wanting the LATEST date used, then change the formula I gave
=IF(A3="","",NETWORKDAYS(A3,IF(COUNT(B323),MIN(B33),$F$2)))
to use MAX instead of MIN
=IF(A3="","",NETWORKDAYS(A3,IF(COUNT(B323),MAX(B33),$F$2)))

But I get an answer of 32 days, not 27, which is exactly the same as when I
physically count work days from the calendar.

Why do you think it should be 27??



Regards

Roger Govier

 

[GORDON]

Roger I also went to the calendar and marked the days. From septembe
the 15 to the 20th is 4 workdays. From September 28 to October 28th i
23 days so the total days from start to finish if the formula wa
working correctly should give me an answer of 27 days

 

[Roger Govier]

Aaaah!!!
But as I said earlier Gordon, I was only guessing at your requirement.
Nowhere have you stated in plain terms what you are trying to achieve.
It now sounds like one has to deduct any period of time between Date Out, 20
Sep, and Letter Out 28 Sep. Is this correct?
(Incidentally, I make 28/9 to 28/10 22 workdays, giving a total of 26)

If this assumption is now correct then the following formula should give you
the correct answers
=IF(A3="","0",NETWORKDAYS(A3,IF(COUNT(B33),MAX(B33),$F$2)))-MAX(0,NETWORKDAYS(B3,C3)-1)

(Note I have changed the value if A3 is empty, from Null to 0, otherwise you
would get a #value error when you deduct 0 from the last part of the
formula. I have also amended the typo in my original formula to make the
range B33, and not B323 as you had already spotted).
I hope this now resolves the issue for you.

Regards

Roger Govier

 

[GORDON]

Roger,

thanks alot that worked out.

 

[Roger Govier]

Hi Gordon
Thanks for the feedback. You're very welcome.
Glad we got there (in the end!!)

Regards

Roger Govier

 

[GORDON]

Roger sorry for all the trouble but I still have a little problem. When
I used:

A...2005-10-11
B...2005-10-12
C...2005-10-24

Your new improved formula gave me answer of 2, where it should have
given me 14. One more crack at it again. Again thanks for all your
help.

 

[Roger Govier]

Hi Gordon

Again I am having difficulty in reconciling to your numbers.
I make it 10 working days between 2005-10-11 and 2005-10-24, not 14

Try
=IF(A3="","0",NETWORKDAYS(A3,IF(COUNT(B33),MAX(B33),$F$2)))-MAX(0,NETWORKDAYS(B3,C3)-1)*(D3<>0)
which is adding a test to see if column D has an entry.

Regards

Roger Govier

 

[GORDON]

Roger, the way i was checking the number of days that it should give m
is that I marked up my paper calendar with the days mentioned. Fro
2005-10-11 to 2005-10-12 equaled 2 days. From 2005-10-24 to (F1 whic
is 2005-11-07) because row D was blank I counted 11 days, then I adde
them up together and came up with 13 days. I was just playing aroun
with the dates to see if the numbers that I input would give me th
correct answer. I knew that this was going to be a problem from th
start because I had tried a long time to find the correct answer that
could trust. This had me puzzled for a long time so I wanted a fres
point of view hoping someone out there may have a clue. I included you
latest solution but that didn't help either. So what needs to happe
is:
If A has a date and B,C,D were blank then networkday A,F
If A has a date and B,C,were blank and D has a date then networkda
A,D
If A and B had a date then networkday A,B
If A,B,C has a date and D was blank then networkday A,B C,F
If A,B,C,D has a date then networkday A,B, C,D
Maybe there is something wrong with Networkdays in that it can't coun
properly. Later on when or if I get this formula to work correctly
will modify it to also exclude Holidays to give me a true count o
days

 

[Roger Govier]

Hi Gordon

I have to go out right now, but will take a look at your problem later.
Glad to have a definition of what you are trying to achieve, rather than
guessing<bg>.

One way in which you may think it is incorrect in its calculation, is, the
days are always inclusive of the start and finish dates, so in some cases
with the dates you used in your original sample, the day would be counted
twice once as part of the A,B calculation, and again as part of the C,D
calculation, which is why in the last formula I posted, there was a -1 inserted.

Regards

Roger Govier

 

[bplumhoff]

Hello,

Thats why I use:

Count of working days (Mondays through Fridays without any holidays):
A1 Start date
A2 End date
Formula:
=(A2-WEEKDAY(A2,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,
WEEKDAY(A2,2))
This formula is different from Excel's built-in function (analysis
add-in). It counts from A1 24:00 until A2 24:00.

Regards,
Bernd

 

[GORDON]

Hi Gordon

I have to go out right now, but will take a look at your proble
later.
Glad to have a definition of what you are trying to achieve, rathe
than
guessing<bg>.

One way in which you may think it is incorrect in its calculation, is
the
days are always inclusive of the start and finish dates, so in som
cases
with the dates you used in your original sample, the day would b
counted
twice once as part of the A,B calculation, and again as part of th
C,D
calculation, which is why in the last formula I posted, there was a -
inserted.

Regards

Roger Govier

Roger, anymore new suggestiions I am still waiting for your answer.
Thank

 

[bplumhoff]

Hi Gordon,

I did not see your question of 27/11/2005 to me since I am no user of
www.excelforum.com. They seem to copy news of the net but seem not to
repost internal answers/questions.

If you send me your file with a short description of what you want to
achieve (I cant see your original .gif file and I like [virus-free!]
excel files) then I might be able to help you soon.

Regards,
Bernd

 

[bplumhoff]

Hi Gordon,

Below you will find my first try. It consists of 3 VBA macros.

Please notice that I only count start dates and not end dates so that
identical end dates with adjacent following start dates do not overlap.

But: If this does not fit your request, then let me know.

Kind regards,
Bernd

----------- snip here -----------------
Option Explicit
'By: Reverse("moc.liborplus.www")
Function cwd(d1 As Date, d2 As Date) As Long
'Counts working days from d1 24:00 until d1 24:00 without holidays
'Different from Excel's networkingdays()!!
cwd = (d2 - Weekday(d2, 2) + Weekday(d1, 2) - d1) / 7 * 5 - min(5, _
Weekday(d1, 2)) + min(5, Weekday(d2, 2))
End Function

Function min(v1 As Variant, v2 As Variant) As Variant
min = v1
If v2 < v1 Then
min = v2
End If
End Function

Function CWPeriods(dBase As Date, rStartEnd As Range) As Variant
'Counts working days between alternating start dates and end dates in
rStartEnd.
'If a start date is given but no corresponding end date then count
until dBase.
'In general: start dates are counting, end dates not!
Dim lc As Long
Dim i As Long, j As Long
Dim bIsStart As Boolean, bEndSeen As Boolean
Dim dStart As Date, dEnd As Date

If rStartEnd.Cells.Count Mod 2 <> 0 Then
CWPeriods = "Range with start dates and end dates has to have even
number of cells!"
Exit Function
End If

bIsStart = True 'First cell in rStartEnd is a start date
bEndSeen = True 'We do not want to count anything if rStartEnd is
empty.
lc = 0 'Initialize day count to zero

For i = 1 To rStartEnd.Rows.Count
For j = 1 To rStartEnd.Columns.Count
If Not IsEmpty(rStartEnd.Cells(i, j)) Then
If bIsStart Then
dStart = rStartEnd.Cells(i, j) - 1
bEndSeen = False
Else
dEnd = rStartEnd.Cells(i, j) - 1
If dEnd < dStart Then
CWPeriods = "Error! End date " & Format(dEnd + 1,
"yyyy-mm-dd") & _
" is before start date " & Format(dStart + 1,
"yyyy-mm-dd") & "."
Exit Function
End If
lc = lc + cwd(dStart, dEnd)
bEndSeen = True
End If
End If
bIsStart = Not bIsStart 'Toggle between start and end date
treatment
Next j
Next i

If Not bEndSeen Then
lc = lc + cwd(dStart, dBase - 1)
End If

CWPeriods = lc

End Function
----------- snip again ---------------------

 

[Roger Govier]

Hi Gordon

Maybe
=IF(A3="","0",NETWORKDAYS(A3,IF(COUNT(B3,D3),MAX(B3,D3),$F$2)))+NETWORKDAYS(C3,$F$2)*(D3=0)-1*(B3=C3)*(B3<>"")

Regards

Roger Govier