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