WORKDAYS formula in excel

  • Thread starter Thread starter Clareabella
  • Start date Start date
C

Clareabella

I am using the workdays formula in excel. It works fine with the exception
of months where there are bank holidays it doesn't seem to be recognising
them. I assume it is not seeing some form of calendar so how to I activate
the calendars in excel (the calendar in outlook is fine and all bank holidays
are showing) ?
 
Hi,

Workday has an optional parameter called holidays

=WORKDAY(A1,B1,holidays)

Where holidays is a named range containing holiday dates
Mike
 
Mike

Thanks for the prompt response. Unfortunately this doesn't actually sort my
problem, the optional parameter "HOLIDAYS" as I understand it allows holiday
dates to be ignored. I actually want them to be considered and seen as a
"non-workday". Do you have anymore info ?
 
<I actually want them to be considered and seen as a "non-workday". >

That's exactly what this parameter does
 
If they are ignored isn't that the same as being considered a non-workday?

Excel has no idea which days are holidays so you must enter those dates in a
range which you name "holidays" or just enter the range in your formula.

=workday(startdate,number,range)


Gord Dibben MS Excel MVP
 
Thank you very much for the comments, I now realise that I was
missunderstanding how the help text defined "exclude". However can someone
try this on their own excel as Jan, Feb, March for 2009 are working fine but
the formula for April is defining the the 2nd working day as the 3rd not the
2nd. I am using the start date as the 1st of the month and then my formula
reads as follows: - =workday(startdate,2,[holidays for Easter 10th, 13th]),
this returns 3rd April and when I change the ,2, for 10 it returns the 17th
April, instead of the 16th. However the formula for January with the holiday
date as 1st Jan returns the correct result as 5th Jan which is the 2nd
working day........I'm very confused, if anyone can help I'll be very
gratefull as I am trying to finalise a meetings calendar for the whole of
2009 for the senior exec at work.
 
I think I've just worked it out, if I am right the formula adds the
"workdays" on to the startdate, therefore for the formula to calculate
working days within any given month to work the start date has to be the last
date of the previous month.
 
You are correct.

WORKDAY does not count the start date in its calculations.


Gord Dibben MS Excel MVP
 
Thank you very much for the comments, I now realise that I was
missunderstanding how the help text defined "exclude". However can someone
try this on their own excel as Jan, Feb, March for 2009 are working fine but
the formula for April is defining the the 2nd working day as the 3rd not the
2nd. I am using the start date as the 1st of the month and then my formula
reads as follows: - =workday(startdate,2,[holidays for Easter 10th, 13th]),
this returns 3rd April and when I change the ,2, for 10 it returns the 17th
April, instead of the 16th. However the formula for January with the holiday
date as 1st Jan returns the correct result as 5th Jan which is the 2nd
working day........I'm very confused, if anyone can help I'll be very
gratefull as I am trying to finalise a meetings calendar for the whole of
2009 for the senior exec at work.
--
Click to expand...

Your results make perfect sense when you consider that Workdays does not
include start_date.

In other words, start_date + 1 will always result in the 1st workday **after**
start_date.

If start_date +1 is a sat, sun or holiday, then it will be "bumped up" to the
next workday.

In the case of Jan (with 1 Jan being a holiday), or 1 Feb 2009 or 1 Mar 2009,
where start_date is also a non-workday (Sunday), the fact that start_date is a
non-workday makes your formula appear to work the way you want to.

In fact, it is working as designed and counting the number of workdays after
start_date.
--ron
 
Back
Top