Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Forums › Off topic › Crazy Excel Formula for Easter Sunday
Lightbulb

119 posts

Master Geek
+1 received by user: 10

ID Verified
Lifetime subscriber

#293763 12-Feb-2022 09:36
Send private message

Excel spreadsheet for determining Easter Sunday.  E4 is the year

 

 

 

=TEXT(DATE(E4,INT((((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7)-7*(INT(((E4-INT(E4/19)*19)+11*((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+22*((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7))/451))+114)/31),((((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7)-7*(INT(((E4-INT(E4/19)*19)+11*((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+22*((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7))/451))+114)-INT((((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7)-7*(INT(((E4-INT(E4/19)*19)+11*((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)+22*((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))-INT((32+2*(INT(E4/100)-INT(INT(E4/100)/4)*4)+2*INT((E4-INT(E4/100)*100)/4)-((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)-INT((19*(E4-INT(E4/19)*19)+INT(E4/100)-(INT(INT(E4/100)/4))-(INT((INT(E4/100)-INT((INT(E4/100)+8)/25)+1)/3))+15)/30)*30)-((E4-INT(E4/100)*100)-INT((E4-INT(E4/100)*100)/4)*4))/7)*7))/451))+114)/31)*31)+1),"d mmmm yyyy")

Create new topic
mkissin
402 posts

Ultimate Geek
+1 received by user: 391

ID Verified
Lifetime subscriber

  #2866771 12-Feb-2022 09:51
Send private message

No, sir, I don't like it.



Geektastic
18009 posts

Uber Geek
+1 received by user: 8465

Trusted
Lifetime subscriber

  #2866790 12-Feb-2022 10:34
Send private message

Very happy to take your word for it!





DjShadow
4222 posts

Uber Geek
+1 received by user: 1322

ID Verified
Trusted
Subscriber

  #2866791 12-Feb-2022 10:38
Send private message

Someone must of been really bored on a night shift



Shadowfoot
First time caller
399 posts

Ultimate Geek
+1 received by user: 256

Trusted
Lifetime subscriber

  #2866902 12-Feb-2022 11:11
Send private message

Sweet. Now to make it work for other lunar festivals. 

 

I do wish Google Calendar let you set recurring events based on lunar events.




Blue Sky: shadowfoot.bsky.social

Shadowfoot
First time caller
399 posts

Ultimate Geek
+1 received by user: 256

Trusted
Lifetime subscriber

  #2866911 12-Feb-2022 11:37
Send private message

Looks like this formula gives the same results. At least for the years 1971-2083 which I tested it on. 

 

=TRUNC(DATE(A2,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(A2,19)+1,1)))/7)*7+8




Blue Sky: shadowfoot.bsky.social

Lightbulb

119 posts

Master Geek
+1 received by user: 10

ID Verified
Lifetime subscriber

  #2866922 12-Feb-2022 12:33
Send private message

Shadowfoot:

 

Looks like this formula gives the same results. At least for the years 1971-2083 which I tested it on. 

 

=TRUNC(DATE(A2,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(A2,19)+1,1)))/7)*7+8

 

 

 

 

Your formula seems to produce a different result to mine from 3001 onwards.

 

 

 

 

 
 
 
 

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.
Behodar
11096 posts

Uber Geek
+1 received by user: 6075

Trusted
Lifetime subscriber

  #2866928 12-Feb-2022 13:12
Send private message

Lightbulb: Your formula seems to produce a different result to mine from 3001 onwards.

 

I'm interested in hearing your business case for needing this data post-3000.

eracode
Smpl Mnmlst
9333 posts

Uber Geek
+1 received by user: 6201

ID Verified
Trusted
Lifetime subscriber

  #2867199 12-Feb-2022 17:17
Send private message

Prithee tell me, what exactly is wrong with “the first Sunday after the full Moon that occurs on or after the spring equinox”? 😀




Sometimes I just sit and think. Other times I just sit.

nzkc
1634 posts

Uber Geek
+1 received by user: 1041


  #2867210 12-Feb-2022 17:28
Send private message

Behodar:

 

Lightbulb: Your formula seems to produce a different result to mine from 3001 onwards.

 

I'm interested in hearing your business case for needing this data post-3000.

 

 

This is what people used to say in the 70s and 80s about 2 digit year dates! 😄

 

 

 

Ive had to deal with calculating Easter before computationally. Many moons ago I stumbled onto this: https://h2g2.com/edited_entry/A653267 (it used to be hosted on the BBC site). It uses integer math only so is pretty fast. Suspect its the same approach as the Excel formula.

Geektastic
18009 posts

Uber Geek
+1 received by user: 8465

Trusted
Lifetime subscriber

  #2867215 12-Feb-2022 17:56
Send private message

This is a lot of effort just so you can know when you'll be able to get chocolate eggs.





Eva888
2762 posts

Uber Geek
+1 received by user: 2426

Lifetime subscriber

  #2867310 12-Feb-2022 23:31
Send private message

Easy to see why your name is @Lightbulb

 
 
 
 

Shop now on AliExpress (affiliate link).
richms
29098 posts

Uber Geek
+1 received by user: 10209

Trusted
Lifetime subscriber

  #2867332 13-Feb-2022 00:13
Send private message

Geektastic: This is a lot of effort just so you can know when you'll be able to get chocolate eggs.

 

You can get the eggs from Feburary onwards, its finding the day that you cant go shopping that it matters.




Richard rich.ms

Create new topic








Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Updates »

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.








RSS feeds
Main feed
Forums feed
Copyright
©2002-2026 Geekzone®
Site features
Geekzone BI dashboard
Geekzone Badges
Geekzone Status Page

 

Site Information
Subscribe to Geekzone
Privacy Statement
Forum Usage Guidelines (FUG)
Advertising
Trademark and copyright