Holidays

[Patrick C. Simonds]

In cell A1 I enter a year.

In cell:

A2 I would like something that will return the date for Memorial Day (Last
Monday of May)
A3 I would like something that will return the date for Labor Day (1st
Monday of September)
A4 I would like something that will return the date for Thanksgiving Day
(4th Thursday of November)

 

[Bernard Liengme]

John Walkenbach's book
http://books.google.com/books?id=Z_...&hl=en&sa=X&oi=book_result&resnum=1&ct=result

Bernard Liengme's book (page 209)
http://books.google.com/books?id=w9...&oi=book_result&resnum=10&ct=result#PPA210,M1

Chip's VBA code
http://www.cpearson.com/excel/holidays.htm

Jon Peltier (Easter)
http://peltiertech.com/WordPress/2008/08/15/calculating-easter/

 

[Bernd P]

Hello,

http://www.sulprobil.com/html/holiday_table.html

Just replace 2007 by A1 and put the formulas into the other cells you
mentioned.

Regards,
Bernd

 

[ShaneDevenshire]

Hi,

i've looked at the JW site and can't make sense out of part of it. So here
is my version of the formula:

=A3+37-WEEKDAY(A3)-(WEEKDAY(A3)<7)*7

I invented this function about 15 years ago, and have never had a chance to
use it. I also haven't tested it for every year, but it works for this year
and the year I created it.

Where A3 contains the year.

If this helps, please click the Yes button.

 

[ShaneDevenshire]

Hi,

I need to correct that the contents of A1 are 5/1/2008 not the year 2008

 

[ShaneDevenshire]

Hi,

I guess I'm getting into this one, here is some additional analysis and
possible solutions:

You can calculate Memorial day by entering the 1st of may in a cell, say B1,
and then using the following formula

=B1+37-WEEKDAY(B1)-(WEEKDAY(B1)<7)*7

If you want this to work for the current year every year you can enter the
following formula in B1:

=DATE(YEAR(TODAY()),5,1)

or if you want to be cute, enter the following in B1

=--("may1")

Which means that the top formula, for the current year becomes:

=--("may1")+37-WEEKDAY(--("may1"))-(WEEKDAY(--("may1"))<7)*7

And if you want to enter the year, 2008 for example, in A2 then the formula
becomes:

=--("may"&A2)+37-WEEKDAY(--("may"&A2))-(WEEKDAY(--("may"&A2))<7)*7

If this helps, please click the Yes button.

 

[Ron Rosenfeld]

In cell A1 I enter a year.

In cell:

A2 I would like something that will return the date for Memorial Day (Last
Monday of May)
A3 I would like something that will return the date for Labor Day (1st
Monday of September)
A4 I would like something that will return the date for Thanksgiving Day
(4th Thursday of November)

Try these:

=DATE(A1,6,0)+1-WEEKDAY(DATE(A1,6,0)+1-2) Last Monday of May
=DATE(A1,9,1)+7-WEEKDAY(DATE(A1,9,1)+7-2) First Monday of Sep
=DATE(A1,11,1)+7-WEEKDAY(DATE(A1,11,1)+7-5)+21 Fourth Thu of Nov
--ron

 

[Shane Devenshire]

Hi,

I didn't think I would be back on this one again, but isn't it interesting
how ideas evolve:

Here are some even shorter suggestions:

Memorial day: =DATE(A2,6,1)-WEEKDAY(DATE(A2,6,1)-2)
Labor day: =DATE(A2,9,8)-WEEKDAY(DATE(A2,9,8)-2)
Thanksgiving: =DATE(A2,11,29)-WEEKDAY(DATE(A2,11,3))

 

[Shane Devenshire]

Hi,

I should have caught this in the last email, these can be shortened yet again:

Memorial day: =DATE(A2,6,1)-WEEKDAY(DATE(A2,5,30))
Labor day: =DATE(A2,9,8)-WEEKDAY(DATE(A2,9,6))

 

[Ron Rosenfeld]

Hi,

I didn't think I would be back on this one again, but isn't it interesting
how ideas evolve:

Here are some even shorter suggestions:

Memorial day: =DATE(A2,6,1)-WEEKDAY(DATE(A2,6,1)-2)
Labor day: =DATE(A2,9,8)-WEEKDAY(DATE(A2,9,8)-2)
Thanksgiving: =DATE(A2,11,29)-WEEKDAY(DATE(A2,11,3))

Shorter, but less comprehensible to someone unfamiliar with the date
manipulations being used.
--ron