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) ?
--
Clareabella

Hi,

Workday has an optional parameter called holidays

=WORKDAY(A1,B1,holidays)

Where holidays is a named range containing holiday dates
Mike

"Clareabella" wrote:

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) ?
--
Clareabella

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 ?
--
Clareabella


"Mike H" wrote:

Hi,

Workday has an optional parameter called holidays

=WORKDAY(A1,B1,holidays)

Where holidays is a named range containing holiday dates
Mike

"Clareabella" wrote:

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) ?
--
Clareabella

<I actually want them to be considered and seen as a "non-workday".

That's exactly what this parameter does

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Clareabella" wrote in message
...
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 ?
--
Clareabella


"Mike H" wrote:

Hi,

Workday has an optional parameter called holidays

=WORKDAY(A1,B1,holidays)

Where holidays is a named range containing holiday dates
Mike

"Clareabella" wrote:

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) ?
--
Clareabella

Try the Excel help for the WORKDAYS function.
--
David Biddulph

Clareabella wrote:
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) ?

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

On Thu, 15 Jan 2009 13:19:02 -0800, Clareabella
wrote:

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 ?

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.
--
Clareabella


"David Biddulph" wrote:

Try the Excel help for the WORKDAYS function.
--
David Biddulph

Clareabella wrote:
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) ?

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.
--
Clareabella


"Gord Dibben" wrote:

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

On Thu, 15 Jan 2009 13:19:02 -0800, Clareabella
wrote:

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 ?

You are correct.

WORKDAY does not count the start date in its calculations.


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 14:36:01 -0800, Clareabella
wrote:

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.

On Thu, 15 Jan 2009 14:22:02 -0800, Clareabella
wrote:

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.
--

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