Weekday Function and getting public holiday date

[JuzMe]

I have 2 related questions to ask:

1. I have a list of dates and I will want to set my next column(Workday) to 1 if the date is monday to saturday but 0 if the date is on a sunday like this:

Screenshot


2. I will like to set my WorkDay to 0 at sheet 1 if the date is a public holiday entered at sheet 2.

Sheet 1

Sheet 2

In conclusion, how am I suppose to write the formula for my workday(column) as shown that meet my 2 requirements stated?

[oldchippy]

Hi JuzMe,

Does this help?

[JuzMe]

That is really helpful. But I got a question for the part 2 of my question.

Is it possible to set my work day to 0 for each holiday added to my sheet 2 automatically?

For instance,

There is a new holiday on 1st of july 2008. Is it possible to set the workday to 0 automatically at sheet 1 once the new holiday is added at sheet 2?

[oldchippy]

Hi,

Is it possible to set the workday to 0 automatically at sheet 1 once the new holiday is added at sheet 2?

Yes by creating a Dynamic named range for the dates on Sheet2, then when ever you add any futher dates to the list the formula on Sheet1 includes them in the Vlookup.

http://www.contextures.com/xlNames01.html#Dynamic

For this to happen on Sheet1 the Vlookup formula includes the "named range" rather than the cell references. In this case it is called "DateList".

See the attached for a working sample

There is a new holiday on 1st of july 2008

Are we talking UK, if so yippee.......more time off!