Workday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I am trying to use the Weekday function to find a week day
after X number of working days from the begining of a
given month. If the start of the month date is a weekend
date then I get an incorrect answer. How can I overcome
this. A formula would be fantastic. Alternatively a
suggestion would be appreciated. I am using UK dates
(Monday as day 1). Thanks in advance.

Kind Regards
 
Hi!

Have you tried the WORKDAY Analysis ToolPak function?

=WORKDAY(DATE(YEAR(A1),MONTH(A1),1),5)

In this case, if the 1st of the month is a Saturday (eg May 2004) or a
Sunday (eg Feb 2004) the formula will return the following Friday.

Analysis ToolPak must be installed and selected as an Addin.
 
Use the workday function

=WORKDAY(A1,10)

start of month in A1 and number of days = 10
you can also add a third parameter which could be a range of holidays
to be excluded

=WORKDAY(A1,10,H1:H10)

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Try this........
With A1 being your first of the month date
and B1 being the number of days after the first, then in C1 put........

=IF(WEEKDAY(A1+B1)=6,WEEKDAY(A1+B1+2),IF(WEEKDAY(A1+B1)=5,WEEKDAY(A1+B1+3),W
EEKDAY(A1+B1+1)))

Vaya con Dios,
Chuck, CABGx3
 
Hi Norman,
Thanks for your reply. I tried the formula in May with 3rd
of May being a holiday. If i want the date after 8 working
days then it shoud be 14 May. However, the formula gives
me 13 May. Here's what I used

=WORKDAY(DATE(YEAR(A1),MONTH(A1),1),A6,Holidays)

Holidays = 3/5/04.

What am I doing wrong? Thanks.
 
Hi!

I make 13-May-2004 as being the right date.

Where the 1st of the Month is a Saturday or Sunday the formula counts
(if required by the number that is added) the following Monday to
Friday. The Monday following 1-May-2004 is declared as a holiday
(pagan / socialist ritual!) and is excluded. So we only have four days
counted in that week leaving 4 more to be added. 4 working days into
the next week is the Thursday of the next week - 13-May-2004.

But I understand your problem! If I use July and declare July 5th as a
holiday (to sober up from July 4?), the formula returns Wed 14-July.
The formula only "counts" the first working day in the days to be
added if the first date in the range is a weekday or holiday. This
must be regarded as a *feature* of WORKDAY rather than an error.

You can adjust for this feature if you want but first establish that
this is what you want to do.
 
Back
Top