Holidays

  • Thread starter Thread starter Patrick C. Simonds
  • Start date Start date
P

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)
 
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.
 
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.
 
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)
Click to expand...

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
 
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))
 
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))
 
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))
Click to expand...

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