PDA

View Full Version : Finding Holidays Formula



Bopo2
11-02-2009, 02:18 PM
Hi All

Well I have a simple spreadsheet calendar (see print screen below) and thanks to the suggestions from the members of the form, I have now used conditional formatting to highlight weekends in a different color for example (print screens outdated)

Anyway, I was wondering if someone knows a formula for working out the bank holidays of a year? I've researched quite a bit on this and I haven't really found anything. Here's the spreadsheet which xlt did which uses conditional formatting to not only find weekends, but current date etc, so if you want to have a play around feel free, as I'm not really getting any closer :doh:.

http://www.vbaexpress.com/forum/showpost.php?p=197979&postcount=3

mbarron
11-02-2009, 07:37 PM
If you have a list of the holidays you can replace the second condition with the following.

=NOT(ISNA(VLOOKUP(DATE($A$1,ROW()-1,B$1),holidays,1,0)))
where holidays is a named range of your holidays.

Sample is attached. The named range has been highlighted

mbarron
11-02-2009, 08:01 PM
Upon further review...

My suggestion must be combined with the former second condition. Otherwise if a holiday occurs on the first of the month following a month with less than 31 days both the first and the 31 of the previous month would be highlighted.


=AND(NOT(ISNA(VLOOKUP(DATE($A$1,ROW()-1,B$1),holidays,1,0))),NOT(MONTH(DATE($A$1,ROW()-1,F$1))<>MONTH(DATE($A$1,ROW()-1,1))))
I apologize for any confusion I may have caused.

Bob Phillips
11-03-2009, 12:07 AM
As mbarron has stated, if you have a list of holidays, it is a simple matter to get the CF to show them, although I would use MATCH not VLOOKUP as the holidys is a single column list.

If you want to calculate the bank holidays, that is usually possible when the rules for when the holidays fall is clear (e.g. Christmas bank holiday falls on a Monday if the actual day is Sat or Sun). Easter and Whitsun are usually tricky, but I have seen code for determining these also.