U.S. Holidays that the date moves

[top.jimmy]

Are there Excel (not VB or VBA) formulas already posted that will give the
dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every
year? A perpetual formula for each so to speak.
New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc.
are easy. They never change.

Anyone who has gone down this road and would like to share their efforts, I
would appreciate it!!!
JIM

 

[Mike H]

Jim

Chip Pearson has done it.

http://www.cpearson.com/excel/holidays.htm

Mike

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/holidays.htm#Easter

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Are there Excel (not VB or VBA) formulas already posted that will give the
| dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every
| year? A perpetual formula for each so to speak.
| New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc.
| are easy. They never change.
|
| Anyone who has gone down this road and would like to share their efforts, I
| would appreciate it!!!
| JIM

 

[top.jimmy]

What am I missing here to calculate Thanksgiving Day which is the 4th
Thursday of the month of November? How do I write DoW?:

=DATE(YEAR(NOW()),11,1+((4th-(DoW>=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1))))

 

[Peo Sjoblom]

Day of the week

If you check your other post I posted formulas for Easter, Memorial, Labor
and Thanksgiving

 

[Mike H]

For thanksgiving put the year in A1 and this formula in a cell

=DATE(A1,11,1+((4-(5>=WEEKDAY(DATE(A1,11,1))))*7)+(5-WEEKDAY(DATE(A1,11,1))))

Mike

 

[Peo Sjoblom]

Here's my post again

Easter day


=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6


(by Tomas Jansen), format as date


for US Memorial day


=DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2)


for US Labor Day


=DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6))


for US Thanksgiving


=DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))

 

[top.jimmy]

SWEET!!! I'm assuming A1 contains the year, right?

 

[Peo Sjoblom]

Correct


--
Regards,

Peo Sjoblom

SWEET!!! I'm assuming A1 contains the year, right?

 

[T. Valko]

How many posts are there?

I posted formulas for Labor Day and Memorial day at one of his threads in
..excel.

 

[Peo Sjoblom]

The one I posted to was one of those strange things where it seems he posted
to an old post from 2006 which you btw took part in (I noticed that after I
posted my answer).

http://tinyurl.com/2gfale

if you scroll to the top you will find yourself in that thread from Nov 2006
<g>

Peo

 

[T. Valko]

I see!

Now I'm in all 3 of his threads!

 

[ilia]

I never understood this. This was an entry for the shortest formula
to return the correct Easter date, right? Why use the DOLLAR()
function? ROUND() works as well from what I can tell, and it's one
character shorter. Is this just an obfuscation technique?

 

[Peo Sjoblom]

I don't know why, regardless it didn't win. The winning formula by Norbert
Hetterich was

=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34

(converted to US)



--
Regards,

Peo Sjoblom

 

[Peo Sjoblom]

Note that this was a German competition and the DOLLAR function in German is
DM (I assume it stands for Deutsche Mark) and ROUND is RUNDEN so the
difference is 4 characters, so there you might have your explanation

 

[C Gray]

this one works. all in Excel

=DATE(A4,5,1+(((IF(WEEKDAY(DATE(A4,5,1))>=2,IF(WEEKDAY(DATE(A4,5,1))>=7,5,4),5))-(2>=WEEKDAY(DATE(A4,5,1))))*7)+(2-WEEKDAY(DATE(A4,5,1))))

This one returns the Memorial day when the year is in A4



Posted as a reply to:

U.S. Holidays that the date moves

Are there Excel (not VB or VBA) formulas already posted that will give the
dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every
year? A perpetual formula for each so to speak.
New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc.
are easy. They never change

Anyone who has gone down this road and would like to share their efforts, I
would appreciate it!!
JIM

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[C Gray]

Your Memorial day does not work correctly. Memorial day is always on the last Monday in May. On occasion it is on the 5th Monday in May. See my formula below.

=DATE(A3,5,1+(((IF(WEEKDAY(DATE(A3,5,1))>=2,IF(WEEKDAY(DATE(A3,5,1))>=7,5,4),5))-(2>=WEEKDAY(DATE(A3,5,1))))*7)+(2-WEEKDAY(DATE(A3,5,1))))

this one will work correctly.



Posted as a reply to:

Re: U.S. Holidays that the date moves

Here's my post agai

Easter da

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-

(by Tomas Jansen), format as dat

for US Memorial da

=DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2

for US Labor Da

=DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)

for US Thanksgivin

=DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)


--
Regards

Peo Sjoblo



EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[p45cal]

this one works. all in Excel

=DATE(A4,5,1+(((IF(WEEKDAY(DATE(A4,5,1))>=2,IF(WEEKDAY(DATE(A4,5,1))>=7,5,4),5))-(2>=WEEKDAY(DATE(A4,5,1))))*7)+(2-WEEKDAY(DATE(A4,5,1))))

This one returns the Memorial day when the year is in A4



Posted as a reply to:

U.S. Holidays that the date moves

Are there Excel (not VB or VBA) formulas already posted that will giv
the
dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Da
every
year? A perpetual formula for each so to speak.
New Years, Christmas, Valentine's, St. Patrick's, & Independence Days
etc.
are easy. They never change.

Anyone who has gone down this road and would like to share thei
efforts, I
would appreciate it!!!
JIM

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
'WCF Workflow Services Using External Data Exchange
(http://tinyurl.com/nzskmw)

Memorial is last Monday in May, right?
The formula above gives the date as 22nd May 2017, the same applies t
2023 and 2028. These are not the last Mondays.

Try:
=FLOOR(DATE(A4,6,1)+4,7)-

 

[T. Valko]

Neither of those formulas for Memorial Day (last Monday in May) work
correctly.

A - get the date for the 1st Monday in June
B - subtract 7

=DATE(year,6,8)-WEEKDAY(DATE(year,6,6))-7