First working day of the month

I need a formula to calculate the first working day of the month. At present
I am using:

=DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0)))

where B9 is today's date. However, this does not take into account bank
holidays (such as 1 January).

This spreadsheet is for use by other users and needs to be as automated as
possible so I don't want to override the date cell if I can help it. Does
anyone have a simple formula to calculate the first working day of each
month, taking into account bank holidays?

Thanks

Use

=WORKDAY(B9-DAY(B9),1,holidays)

where holidays is a list of holiday dates.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hedgie" wrote in message
news
I need a formula to calculate the first working day of the month. At
present
I am using:

=DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0)))

where B9 is today's date. However, this does not take into account bank
holidays (such as 1 January).

This spreadsheet is for use by other users and needs to be as automated as
possible so I don't want to override the date cell if I can help it. Does
anyone have a simple formula to calculate the first working day of each
month, taking into account bank holidays?

Thanks

If you (and everyone who will use the workbook) has
the Analysis ToolPak add-in installed, you could use
the WORKDAY function.

However, if there is risk that some of the users will NOT have that add-in,
try this:

B9: (a date)
G1:G10 contains a list of bank holidays.....eg G1: 2008-01-01, etc

This formula (in sections, for readability) returns
the 1st workday of the month:

=MIN(IF((COUNTIF(G1:G10,B9-DAY(B9)+{1,2,3,4,5,6,7})=0)*
(WEEKDAY(B9-DAY(B9)+{1,2,3,4,5,6,7},2)6),(B9-DAY(B9)+{1,2,3,4,5,6,7})))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Hedgie" wrote in message
news
I need a formula to calculate the first working day of the month. At
present
I am using:

=DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0)))

where B9 is today's date. However, this does not take into account bank
holidays (such as 1 January).

This spreadsheet is for use by other users and needs to be as automated as
possible so I don't want to override the date cell if I can help it. Does
anyone have a simple formula to calculate the first working day of each
month, taking into account bank holidays?

Thanks