First working day of the month

H

Hedgie

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
 
B

Bob Phillips

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)
 
R

Ron Coderre

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top