One more trouble with Dates

Ralph M

Active Member
Joined
Apr 16, 2002
Messages
496
Can anyone tell me how to write the Date Formula to show the Date for the Saturday after the 2nd Sunday after Easter? Cell "C1" is the year.
Thanks in advance.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Do you know that Easter is calculated by cycles of the moon? This can be done, but its not a simple equation. I have a function to get the date for Easter. Ill post it as soon as I find it.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
If C1 is showing 2004, you can get Good Friday for this year with the following formula from Chip Pearson:

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

Once you know the date for Good Friday, simply add a number to get the date that you want (that depends on how you define “after Easter” – does that mean the Tuesday after Easter Monday?)

Regards,

Mike
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Ok This will get you the date for Easter

x = (((255 - 11 * (xYear Mod 19)) - 21) Mod 30) + 21
Easter = DateSerial(xYear, 3, 1) + x + (x > 48) + 6 - ((xYear + xYear \ 4 + x + (x > 48) + 1) Mod 7)


xYear is just the year you want.

Then just add 20 to this to get the Saturday after two Sundays after Easter.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

So to put this together to a custom function

Public Function Easter(xYear As Integer)

Dim x As Integer
Dim EasterDate As Date

x = (((255 - 11 * (xYear Mod 19)) - 21) Mod 30) + 21
Easter = DateSerial(xYear, 3, 1) + x + (x > 48) + 6 - ((xYear + xYear \ 4 + x + (x > 48) + 1) Mod 7)

End Function

So you could put

=Easter(C1) +20

For the date you want

You could do this with a normal function, but it would be a mess.
 

Ralph M

Active Member
Joined
Apr 16, 2002
Messages
496
Thanks everyone, I have the Date for Easter and I'll just add 20 to that.
Thanks again for all of your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top