Holiday rules for Memorial Day Dates

O

OC

I built a self adjusting work schedule and calendar and thought I'd throw in
the holidays from outlook's holiday file. Then I decided to work out the
formulas to have the dates adjust automatically. Worked out great until I
got to Memorial Day and Easter. I decided to scrap Easter due to it being
based on the lunar cycle, had no idea how to calculate that one. It turns
out all the rest had a set date or were a set day per week of month. When it
comes to Memorial Day however the rule is the last Monday of May. Well this
changes between the 4th and 5th weeks and i can't figure it out.

Here's an example of the formula I use for Mother's Day which is the second
Sunday in May:

=IF(DATE(YEAR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7>DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YEAR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7,DATE(YEAR(NOW())+1,5,1)+7-WEEKDAY(DATE(YEAR(NOW())+1,5,1)-DAY(DATE(YEAR(NOW())+1,5,1))+8-1)+(2-1)*7)


Any help you can give me is as always apprciated...
 
J

Jim Cone

This approach may appeal after seeing that formula you wrote.
Paste the following vba code in a standard module and then enter
"= LastDay(A1)" in any cell. The cell reference entered in the
formula should contain a valid date within the month you are searching.

So if A1 contains "=Today()" the answer shown in the cell is 11/ 27/ 2006.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Function LastDay(ByRef dteDay As Variant) As Variant
'Jim Cone - San Francisco, USA - November 2006
'Finds the last particular weekday in a month,
'given a valid date containing the month.
'A weekday in any month is limited to a maximum of 5
Dim D As Long
Dim N As Long
Dim lngLast As Long
Dim lngAnswer As Long
Const dteDesired As Long = vbMonday 'CHANGE as needed
Const lngOccurance As Long = 5 'Use 5 for last occurance - CHANGE as needed

If IsDate(dteDay) = False Then
LastDay = "Please enter valid date"
Exit Function
Else
dteDay = DateSerial(Year(dteDay), Month(dteDay), 1)
End If
lngLast = 32 - Day(dteDay - Day(dteDay) + 32)

For D = 0 To lngLast
lngAnswer = Weekday(dteDay + D)
If lngAnswer = dteDesired Then
If (Day(dteDay) + D) > lngLast Then Exit For
LastDay = dteDay + D
N = N + 1
If N = lngOccurance Then Exit For
End If
Next
End Function
'-----------------


"OC" <[email protected]>
wrote in message
I built a self adjusting work schedule and calendar and thought I'd throw in
the holidays from outlook's holiday file. Then I decided to work out the
formulas to have the dates adjust automatically. Worked out great until I
got to Memorial Day and Easter. I decided to scrap Easter due to it being
based on the lunar cycle, had no idea how to calculate that one. It turns
out all the rest had a set date or were a set day per week of month. When it
comes to Memorial Day however the rule is the last Monday of May. Well this
changes between the 4th and 5th weeks and i can't figure it out.

Here's an example of the formula I use for Mother's Day which is the second
Sunday in May:

=IF(DATE(YEAR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7>DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YEAR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7,DATE(YEAR(NOW())+1,5,1)+7-WEEKDAY(DATE(YEAR(NOW())+1,5,1)-DAY(DATE(YEAR(NOW())+1,5,1))+8-1)+(2-1)*7)


Any help you can give me is as always apprciated...
 
B

Biff

To find the last weekday date in a month:

=DATE(YEAR,MONTH+1,1)-WEEKDAY(DATE(YEAR,MONTH+1,1)+X)

Where X =

1 - Fri
2 - Thur
3 - Wed
4 - Tue
5 - Mon
6 - Sun
7 - Sat

So, for the last Monday in May 2007:

=DATE(2007,5+1,1)-WEEKDAY(DATE(2007,5+1,1)+5)

Biff
 
S

Sandy Mann

OC said:
got to Memorial Day and Easter. I decided to scrap Easter due to it being
based on the lunar cycle, had no idea how to calculate that one.
Click to expand...

You and me both but here is a post by the late George Simms from 4 Dec 2000:

Start of George's Post
*************************************************************
Hi Anthony,

This formula was by Thomas Jansen from Germany. It came second in a contest
to find the shortest formula to calculate the date for Easter Sunday for
any year between 1900 to 2078. You will need to ask him the "how" and
"why" it works.

The winning formula was by Norbert Hetterich from Germany with :

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

Again I don't know how and why it works, but it does.

The contest was run by Hans Herber http://www.herber.de

BTW. I came 20th (Last) <bg>

All the Best

George Simms
Microsoft MVP
Newcastle upon Tyne
England
*****************************************************
End of Geogre's post

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
O

OC

Sandy,
What would go in A1? I've been playing around with it and can't get
anything to work. Got any ideas?

Thanks,
OC
 
S

Sandy Mann

Sorry OC, I should have said. A1 contains the year as a plain number ( ie
2006 or 2007 etc) therefore Easter in 2078 will be on April 3rd.

I just can't wait to see if it is right! <g>

Actually George only said that it was correct up to 2078. I don't know if
that was only as far as he went but it agrees with a Easter calculator that
I found on the net for the year 3050.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
J

Jim Cone

Out here in the colonies, I had to switch the position of
the month and day for it to work. No, I haven't figured it out yet.
=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Sandy Mann" <[email protected]>
wrote in message
Sorry OC, I should have said. A1 contains the year as a plain number ( ie
2006 or 2007 etc) therefore Easter in 2078 will be on April 3rd.
I just can't wait to see if it is right! <g>
Actually George only said that it was correct up to 2078. I don't know if
that was only as far as he went but it agrees with a Easter calculator that
I found on the net for the year 3050.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
S

Sandy Mann

Thank you for converting the formula Jim, that may well be the OP's problem.
I keep forgetting that everyone in the world does not work the same way as
we do.........
months then days......
commas in place of periods.........
semi-colons in place of commas..........
Macs in place of Windows.......
to say nothing of spelling......

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
O

OC

Thanks Jim and Sandy
It's funny because I didn't even think the 5 related to May because Easter
is always in Mar or April. I thought it was trying to divide by 5 and I
didn't understand the "". Mixing text and numbers wasn’t making any sense to
me. It works for the next five years that I checked.
 
O

OC

This was the info I was looking at and hadn't even really gotten a chance to
study it. It's from:

http://aa.usno.navy.mil/faq/docs/easter.html

Computing the Date of Easter
The rule is that Easter is the first Sunday after the first ecclesiastical
full moon that occurs on or after March 21. The lunar cycles used by the
ecclesiastical system are simple to program. The following algorithm will
compute the date of Easter in the Gregorian Calendar system.

The algorithm uses the year, y, to give the month, m, and day, d, of Easter.
The symbol * means multiply.

Please note the following: This is an integer calculation. All variables are
integers and all remainders from division are dropped. For example, 7 divided
by 3 is equal to 2 in integer arithmetic.


c = y / 100
n = y - 19 * ( y / 19 )
k = ( c - 17 ) / 25
i = c - c / 4 - ( c - k ) / 3 + 19 * n + 15
i = i - 30 * ( i / 30 )
i = i - ( i / 28 ) * ( 1 - ( i / 28 ) * ( 29 / ( i + 1 ) )
* ( ( 21 - n ) / 11 ) )
j = y + y / 4 + i + 2 - c + c / 4
j = j - 7 * ( j / 7 )
l = i - j
m = 3 + ( l + 40 ) / 44
d = l + 28 - 31 * ( m / 4 )


For example, using the year 2010,
y=2010,
c=2010/100=20,
n=2010 - 19 x (2010/19) = 2010 - 19 x (105) = 15, [see note above
regarding integer calculations]
etc. resulting in Easter on April 4, 2010.
 
S

Sandy Mann

Yes that is the same site that I found the calculation left me just as
baffled but fortunately there is a link to a calculator at:

http://aa.usno.navy.mil/data/docs/easter.html

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


OC said:
This was the info I was looking at and hadn't even really gotten a chance
to
study it. It's from:

http://aa.usno.navy.mil/faq/docs/easter.html

Computing the Date of Easter
The rule is that Easter is the first Sunday after the first ecclesiastical
full moon that occurs on or after March 21. The lunar cycles used by the
ecclesiastical system are simple to program. The following algorithm will
compute the date of Easter in the Gregorian Calendar system.

The algorithm uses the year, y, to give the month, m, and day, d, of
Easter.
The symbol * means multiply.

Please note the following: This is an integer calculation. All variables
are
integers and all remainders from division are dropped. For example, 7
divided
by 3 is equal to 2 in integer arithmetic.


c = y / 100
n = y - 19 * ( y / 19 )
k = ( c - 17 ) / 25
i = c - c / 4 - ( c - k ) / 3 + 19 * n + 15
i = i - 30 * ( i / 30 )
i = i - ( i / 28 ) * ( 1 - ( i / 28 ) * ( 29 / ( i + 1 ) )
* ( ( 21 - n ) / 11 ) )
j = y + y / 4 + i + 2 - c + c / 4
j = j - 7 * ( j / 7 )
l = i - j
m = 3 + ( l + 40 ) / 44
d = l + 28 - 31 * ( m / 4 )


For example, using the year 2010,
y=2010,
c=2010/100=20,
n=2010 - 19 x (2010/19) = 2010 - 19 x (105) = 15, [see note above
regarding integer calculations]
etc. resulting in Easter on April 4, 2010.



OC said:
Thanks Jim and Sandy
It's funny because I didn't even think the 5 related to May because
Easter
is always in Mar or April. I thought it was trying to divide by 5 and I
didn't understand the "". Mixing text and numbers wasn't making any
sense to
me. It works for the next five years that I checked.
Click to expand...
Click to expand...
 
O

OC

If you guys are still there I could sure some help.

This is the formula I came up with to auto calc easter day:
=IF(FLOOR(CONCATENATE("5/",DAY(MINUTE(YEAR(NOW())/38)/2+56),"/",YEAR(NOW())),7)-34>DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATENATE("5/",DAY(MINUTE(YEAR(NOW())/38)/2+56),"/",YEAR(NOW())),7)-34,FLOOR(CONCATENATE("5/",DAY(MINUTE((YEAR(NOW())+1)/38)/2+56),"/",(YEAR(NOW())+1)),7)-34)

The problem I had is when I went to validate. The way I do it is to change
my computer date and then recalc xl. This is the only time i can remember
having to press recalc twice to get it to work. It didn't happen every time
but often enough to baffle me. I was wondering if you guys had ever run into
this?
BTW did you guys get my e-mail?
 
J

Jim Cone

Put 2006, 2007, 2008 in A1, A2, A3 etc.
In B1, enter the modified formula I posted yesterday...
"=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34"
and fill down. You are done.

Most email address's posted in the newsgroups are adulterated.
In mine, you must remove the XXX.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"OC" <[email protected]>
wrote in message
If you guys are still there I could sure some help.

This is the formula I came up with to auto calc easter day:
=IF(FLOOR(CONCATENATE("5/",DAY(MINUTE(YEAR(NOW())/38)/2+56),"/",YEAR(NOW())),7)-34>DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATENATE("5/",DAY(MINUTE(YEAR(NOW())/38)/2+56),"/",YEAR(NOW())),7)-34,FLOOR(CONCATENATE("5/",DAY(MINUTE((YEAR(NOW())+1)/38)/2+56),"/",(YEAR(NOW())+1)),7)-34)

The problem I had is when I went to validate. The way I do it is to change
my computer date and then recalc xl. This is the only time i can remember
having to press recalc twice to get it to work. It didn't happen every time
but often enough to baffle me. I was wondering if you guys had ever run into
this?
BTW did you guys get my e-mail?
 
S

Sandy Mann

Thank you David. I should have known that George would have had a very good
reason for specifying the limit.
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
O

OC

Jim,
I'd like to say I appreciate the help, I really do, but if you had read my
comment you will have realized that I adapted the revised formula you gave me
to auto update based on your computers internal date. I wasn't looking for a
simple one time table. I have a calendar and work schedules where the dates
update automatically and list the holidays/appointments etc... What I was
working on was the rules for all the holidays to update to next years date
once the current month has past. I ran into three problems; Easter, Memorial
Day, and Tax Day because of the unusual rules for the dates. It works like a
champ! The latest problem I had was during validation where I had to recalc
more then once to get the formula to work, that's all. I have never run into
this before and was wondering if anyone else had.

Thanks,
OC
 
T

top.jimmy

What did you come up with for Memorial Day, Thanksgiving Day, and Labor Day?
I'm working on something similar and could use your expertise.

Thanks,
JIM
 
P

Peo Sjoblom

The formula you quoted is far too big,


with the year question in A1


Easter day


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


by Tomas Jansen, format as date


for 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))


--


Regards,


Peo Sjoblom
 
O

OC

Memorial Day:

=IF(DATE(YEAR(NOW()),5+1,0)-WEEKDAY(DATE(YEAR(NOW()),5+1,0)+5)>DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YEAR(NOW()),5+1,0)-WEEKDAY(DATE(YEAR(NOW()),5+1,0)+5),DATE(YEAR(NOW())+1,5+1,0)-WEEKDAY(DATE(YEAR(NOW())+1,5+1,0)+5))

Thanksgiving Day:

=IF(DATE(YEAR(NOW()),11,1)+7-WEEKDAY(DATE(YEAR(NOW()),11,1)-DAY(DATE(YEAR(NOW()),11,1))+8-5)+(4-1)*7>DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YEAR(NOW()),11,1)+7-WEEKDAY(DATE(YEAR(NOW()),11,1)-DAY(DATE(YEAR(NOW()),11,1))+8-5)+(4-1)*7,DATE(YEAR(NOW())+1,11,1)+7-WEEKDAY(DATE(YEAR(NOW())+1,11,1)-DAY(DATE(YEAR(NOW())+1,11,1))+8-5)+(4-1)*7)

Labor Day:
=IF(DATE(YEAR(NOW()),9,1)+7-WEEKDAY(DATE(YEAR(NOW()),9,1)-DAY(DATE(YEAR(NOW()),9,1))+8-2)+(1-1)*7>DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YEAR(NOW()),9,1)+7-WEEKDAY(DATE(YEAR(NOW()),9,1)-DAY(DATE(YEAR(NOW()),9,1))+8-2)+(1-1)*7,DATE(YEAR(NOW())+1,9,1)+7-WEEKDAY(DATE(YEAR(NOW())+1,9,1)-DAY(DATE(YEAR(NOW())+1,9,1))+8-2)+(1-1)*7)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

number of months and days between two dates without approximation 3
Extra help on Nth Day of month 1
Search closest day in the past from today 2
Check dates against a date range. 0
Working out a future date with formulas 3
Find next Sunday 7
week numbers 7
Count the remaining weekdays in a data range excluding holidays and start/end dates 5

Top