Results 1 to 14 of 14
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to calculate Easter (All?)

    Have you ever wanted to use Excel to calculate what date Easter Sunday falls on but found that it does not include a built in way to do it?

    The following 3 formulas all calculate the date for Easter Sunday for the years 1900-2078 and were the result of a competition held in 1999 by Hans Herber.

    How do they work? I have no idea - anyone out there know?

    The year is entered in cell A1
    =FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
    =DOLLAR((DAY(MINUTE(A1/38)/2+55)&"/4/"&A1)/7,)*7-6
    =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to calculate Easter (All?)

    No doubt Easter was invented by the ubiquitous Dr John Stockton.
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to calculate Easter (All?)

    Thanks John

    If only all algorithms were that simple <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to calculate Easter (All?)

    The first two get a #Value error on my XL2K.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to calculate Easter (All?)

    They assume the date seperator is a /

    Try the following variants with seperator changed to a period.

    =FLOOR(DAY(MINUTE(A1/38)/2+56)&".5."&A1,7)-34
    =DOLLAR((DAY(MINUTE(A1/38)/2+55)&".4."&A1)/7,)*7-6

    Another possibilty is as this competition was held in Germany the formula could be d-m-y based rather than m-d-y

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to calculate Easter (All?)

    Thanks Steve

    I have just experimented with various regional settings and though the third formula worked with most settings that I tried it still failed for some. Some failed due to d-m-y & m-d-y issues, others failed due to the date separator (and some did not like both!). An example of how international settings can cause problems with Excel.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to calculate Easter (All?)

    I hate to be dense, but I don't understand what the Dollar function is doing. I get the correct date from the Floor function. What are the next two doing? i get #Value for those.
    I used Steve's variation: =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
    The others didn't work for me.

    But this is great thanks for providing.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to calculate Easter (All?)

    Try this, I made a mistake when I moved the month in my original post

    <pre>=DOLLAR(("4/"&DAY(MINUTE(A1/38)/2+55)&"/"&A1)/7,)*7-6</pre>


    Dollar is just rounding the value to the nearest dollar. You could also use ROUND function.

    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to calculate Easter (All?)

    <P ID="edit" class=small>(Edited by sdckapr on 30-Mar-04 14:24. Corrected error in 2nd formula)</P>They assume d/m/y. try these:
    =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
    =DOLLAR(("4/"&DAY(MINUTE(A1/38)/2+55)&"/"&A1)/7,)*7-6

    Steve

  10. #10
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: How to calculate Easter (All?)

    Here is a user-defined function at http://www.barasch.com/excel/cEa21184.htm. What I want is the calculation for Jewish holidays.
    Alan

  11. #11
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to calculate Easter (All?)

    Hello,

    Well, I must be missing something. All of these formulas refer to cell A1. What's in A1, and in which cell are these formulas supposed to go?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to calculate Easter (All?)

    Cell A1 should contain the year for which you want to determine the date of Easter.
    The formula could go into any other cell on the same worksheet (you need only one of the formulas, not all of them.)

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to calculate Easter (All?)

    Check out this site. It has routines and an addin

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to calculate Easter (All?)

    There's a function =EASTERDATE, along with 40 other useful-to-some functions in the excellent add-in below. The help file claims it's ok up to the year 9999, I find it only works to 2078. Perhaps the SP3 service pack to xl2002 has 'improved' excel's performance?
    http://longre.free.fr/english/
    I got it for the =NUMTEXT formula, which reads a numeric value in a cell and writes it in English words, with a currency word if you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •