3 Replies Latest reply: May 15, 2014 10:04 AM by thecontroller _ RSS

    Formula to Calculate Easter

    thecontroller _

      Hello,

       

      The following formula calculates Easter Sunday in Excel where B2 is the year:

      The format of the date is (mm/dd/yyyy)

       

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

       

      This function renders a serial number in Excel that can be formatted to display in date format.

       

      I am trying to determine when Easter Sunday falls when given any year.

      Let us suppose that I am trying to determine when Easter Sunday is in 2009.

      (It's April 12, 2009 BTW)

       

      How do I write the equivalent formula in Monarch?

       

      Thanks,

        • Formula to Calculate Easter
          Nick Osdale-Popa

          I can get you to the Excel Serial[/I] Number for the date as such (Note all formulas require V10):

           

          round((DateSerial1900(CTOD("4/1/"trim(str(year)))))/7MOD(19MOD(Year,19)-7,30)(.14),0)*7-6[/code]

           

          Now we just need to be able to convert the Serial Number to a proper date format:

          DateAdjust({12/30/1899},0,0,Easter)[/code]

           

          To make it all one formula:

          DateAdjust({12/30/1899},0,0,round((DateSerial1900(CTOD("4/1/"trim(str(year)))))/7MOD(19MOD(Year,19)-7,30)(.14),0)*7-6)[/code]

           

          That should do it.

            • Formula to Calculate Easter
              Grant Perkins

              Another possibility, since the rule makes pre-calculation of the date fairly certain, is to run the formula in Excel for a number of years and then use the resulting information for a lookup, either internal or external.

               

              Creating an internal lookup would allow the potential for storing the Easter Dates object in a central model ready to re-use whenever it is required.

               

               

              Just a thought.

               

               

              Grant

                • Formula to Calculate Easter
                  thecontroller _

                  Nick,

                   

                  Thank you for translating the formula.  It works beautifully.

                   

                  BTW, for those following, the Excel formula translated was another Excel formula that calculates Easter and it is:

                   

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

                   

                  Grant,

                   

                  I also like the idea of lookups because less computations means more efficient models.

                   

                  Thanks,