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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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"
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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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

"OC" 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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

"OC" wrote in message
...

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.

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


with @tiscali.co.uk

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


"Sandy Mann" wrote:

"OC" wrote in message
...

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.

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


with @tiscali.co.uk

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


with @tiscali.co.uk


"OC" wrote in message
...
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


"Sandy Mann" wrote:

"OC" wrote in message
...

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.

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


with @tiscali.co.uk

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"
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

with @tiscali.co.uk



"OC"
wrote in message
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

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


with @tiscali.co.uk


"Jim Cone" wrote in message
...
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"
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

with @tiscali.co.uk



"OC"
wrote in message
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

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 wasnt making any sense to
me. It works for the next five years that I checked.


"Jim Cone" wrote:

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"
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

with @tiscali.co.uk



"OC"
wrote in message
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

For 2079 it gives 16th April, whereas the formula from
http://aa.usno.navy.mil/faq/docs/easter.html gives 23 April. The latter
date is confirmed by http://www.ely.anglican.org/cgi-bin/easter, so I think
that the limitation to 2078 was deliberate.
--
David Biddulph

"Sandy Mann" 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


with @tiscali.co.uk


"OC" wrote in message
...
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


"Sandy Mann" wrote:

"OC" wrote in message
...

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.

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


with @tiscali.co.uk

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


with @tiscali.co.uk


"David Biddulph" wrote in message
...
For 2079 it gives 16th April, whereas the formula from
http://aa.usno.navy.mil/faq/docs/easter.html gives 23 April. The latter
date is confirmed by http://www.ely.anglican.org/cgi-bin/easter, so I
think that the limitation to 2078 was deliberate.
--
David Biddulph

"Sandy Mann" 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


with @tiscali.co.uk


"OC" wrote in message
...
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


"Sandy Mann" wrote:

"OC" wrote in message
...

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.

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


with @tiscali.co.uk

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)-34DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATEN ATE("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?

"OC" wrote:

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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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"
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)-34DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATEN ATE("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?

"OC" wrote:

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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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


"Jim Cone" wrote:

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"
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)-34DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATEN ATE("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?

"OC" wrote:

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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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

"OC" wrote:

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)-34DATE(YEAR(NOW()),MONTH(NOW()),1),FLOOR(CONCATEN ATE("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?

"OC" wrote:

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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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...

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

"top.jimmy" wrote in message
...
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

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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(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)