issue with Workday Function - not calculating workdays correctly!

[amran.majid]

From: (e-mail address removed)
Newsgroups: microsoft.public.excel
Subject: issue with Workday Function - not calculating workdays
correctly!
Date: Thu, 05 Oct 2006 12:23:03 -0000

hi,

trying to use the workday function to calculate the number of workdays
from a specific date.

i am trying to calculate the 11th working day for each month.

it works for October, but not november and december 2006.

A1 = 01/10/2006
B1 = 02/11/2006
C1 = 03/12/2006

A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 which is
correct.
B2 = WORKDAY(A1,11,Holidays) returns a value of 16/11/2006 which is
incorrect. it should be 15/11/2006 (wed 01/11 - wed 15/11)
C2 = WORKDAY(A1,11,Holidays) returns an uncorrect value of 18/12, it
should be 15/12/2006.

curiously January 2007 is ok on 16/01/2007

whats going on?

 

[Bob Phillips]

You seem to be confused.

All of the formulae that you give, in A2, B2, and C2, will give 16/10/2006,
because they all source from A1.

If you really mean

B2 = WORKDAY(B1,11,Holidays) gives 17/11 which is correct and
C2 = WORKDAY(C1,11,Holidays) gives 18/12, which is also correct



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[amran.majid]

Bob,

sorry yes your right i just copied and pasted wrong, and put the wrong
dates in as starters in my example...! doh! i have corrected the info
below to the correct start dates and correct formula reference.

A1 = 01/10/2006
B1 = 01/11/2006
C1 = 01/12/2006

A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 - correct.
B2 = WORKDAY(B1,11,Holidays) returns a value of 16/11/2006 -
incorrect. it should be 15/11/2006 (wed 01/11 - wed 15/11)
C2 = WORKDAY(C1,11,Holidays) incorrect 18/12, it should be 15/12/2006.

18/12 is not the 11th working day of december however as you can see
from the table below

01/12/2006 Fri 1
02/12/2006 Sat NWD (not working day)
03/12/2006 Sun NWD
04/12/2006 Mon 2
05/12/2006 Tue 3
06/12/2006 Wed 4
07/12/2006 Thu 5
08/12/2006 Fri 6
09/12/2006 Sat NWD
10/12/2006 Sun NWD
11/12/2006 Mon 7
12/12/2006 Tue 8
13/12/2006 Wed 9
14/12/2006 Thu 10
15/12/2006 Fri 11
16/12/2006 Sat NWD
17/12/2006 Sun NWD
18/12/2006 Mon 12
19/12/2006 Tue 13

 

[Bob Phillips]

I now think you misunderstand what WORKDAY does. It does not return the nth
working day of a month, it returns n working days AFTER the start date.
Because the 1st Dec is a working day, 11 days AFTER that is the 12th working
day, so 18/12 is absolutely correct.

It just seems right by your interpretation for October because 01/10 is a
Sunday.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[amran.majid]

I now think you misunderstand what WORKDAY does. It does not return the nth
working day of a month, it returns n working days AFTER the start date.
Because the 1st Dec is a working day, 11 days AFTER that is the 12th working
day, so 18/12 is absolutely correct.

It just seems right by your interpretation for October because 01/10 is a
Sunday.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

ah! i understand. any ideas how i could calculate the number of working
days from a particluar date?

i assume i could use EOMONTH to calc the last day and then use the
WORKDAY function to calculate it..

 

[Bob Phillips]

That is what WORKDAY does, it calculates a date a number of working days
past a date. If you want the nth working day of a month, just start at the
last day of the previous month.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[amran.majid]

just so you know used EOMONTH and based the wrokday cal on that and it
seems to work.

Thanks for the pointer.

 

[Ron Rosenfeld]

ah! i understand. any ideas how i could calculate the number of working
days from a particluar date?

i assume i could use EOMONTH to calc the last day and then use the
WORKDAY function to calculate it..

No need to use EOMONTH if you have the first day of the month in A1:

=WORKDAY(A1-1,11)

or, for any day of the month in A1:

=WORKDAY(A1-DAY(A1),11)




--ron