Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Easter day formula

easter day formula excel Sample Spreadsheets in spanish

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

'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6

This is what i got but does not work in spanish


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,365
Default Easter day formula

Could it be that the month/day sequence is different where it's not working?

I'd be interested in seeing entire the working solution. I did some VBA
code to calculate Easter and it seems to me that all I've seen points to the
solution being a bit more complex than that. Maybe I'm wrong?

Here's code for a UDF that will calculate Easter Sunday for any year after
1582.
Call it by passing a year or the address of a cell with the year in it, as
=NewDetermineEasterSunday(2007)
or
=NewDetermineEasterSunday(A1)

Function NewDetermineEasterSunday(anyYear As Variant) As Variant
'Determine Easter Sunday for any year after 1583
'INPUT: The Year
'OUTPUT: a DateSerial value that can be used to display
'the date in any format chosen by the end user
'ERROR OUTPUT: "Invalid Year" will be returned if an
'invalid input is provided.
'note, integer math (\ vs /) is critical to accurate results
'extra care has been made in places to assure integer math
'is performed thoughout the process
'
Dim anyYearValue As Variant
Dim myYear As Integer
Dim Century As Integer
Dim GoldenNumber As Integer
Dim GregorianFix As Integer
Dim ClavianFix As Integer
Dim Epact As Integer
Dim FindSundays As Integer
Dim DaysIntoMarch As Integer

anyYearValue = Val(anyYear)
If Int(anyYearValue) < anyYearValue Then
NewDetermineEasterSunday = "Invalid Year"
Exit Function
End If
If anyYearValue < 1583 Then
NewDetermineEasterSunday = "Invalid Year"
Exit Function
End If
myYear = Int(anyYearValue) ' move to working integer value
Century = (myYear \ 100) + 1
GregorianFix = ((3 * Century) \ 4) - 12
GoldenNumber = (myYear Mod 19) + 1
ClavianFix = ((8 * Century + 5) \ 25) - 5 - GregorianFix
FindSundays = ((5 * myYear) \ 4) - GregorianFix - 10 '************
Epact = Int((Int(11 * GoldenNumber) + 20 + ClavianFix)) Mod 30
If Epact < 25 Then
If GoldenNumber 11 Then
Epact = Epact + 1
End If
End If
If Epact = 24 Then
Epact = Epact + 1
End If
DaysIntoMarch = 44 - Epact
If DaysIntoMarch <= 20 Then
DaysIntoMarch = DaysIntoMarch + 30
End If
DaysIntoMarch = (DaysIntoMarch + 7) - ((DaysIntoMarch + FindSundays) Mod
7)
NewDetermineEasterSunday = DateSerial(myYear, 3, DaysIntoMarch)
End Function

Some test cases:
1943/2049: April 25
1981: April 19
1984: April 15
2285: March 22
4200: April 20
2007: April 8

"Antonio Atala" wrote:

easter day formula excel Sample Spreadsheets in spanish

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

'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6

This is what i got but does not work in spanish



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 434
Default Easter day formula

hi, Antonio !
[just in case you don't come-back to spanis-ng] :))

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish


1) based on the 'original' winning formula in Hans W. Herber's competition [english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,646
Default Easter day formula

Hi Hector,

Do you know the formula also for date format yyyy/mm/dd? If so, please let
me know it!
Thanks,
Stefi

€žHéctor Miguel€ ezt Ã*rta:

hi, Antonio !
[just in case you don't come-back to spanis-ng] :))

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish


1) based on the 'original' winning formula in Hans W. Herber's competition [english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Easter day formula

couldn't you just use this and format the date the way you want it?

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

--


Gary


"Stefi" wrote in message
...
Hi Hector,

Do you know the formula also for date format yyyy/mm/dd? If so, please let
me know it!
Thanks,
Stefi

"Héctor Miguel" ezt írta:

hi, Antonio !
[just in case you don't come-back to spanis-ng] :))

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish


1) based on the 'original' winning formula in Hans W. Herber's competition
[english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control
panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.







  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 434
Default Easter day formula

hi, Stefi !

Do you know the formula also for date format yyyy/mm/dd? If so, please let me know it!


how about disregarding any date-order and avoid regional settings ?

- floor(date(a1,5,day(minute(a1/38)/2+56)),7)-34

hth,
hector.

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish


1) based on the 'original' winning formula in Hans W. Herber's competition [english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,365
Default Easter day formula

After doing some research and testing, I've found that formula in the
previous post is prone to error at irregular intervals. The following is
based on an algorithm provided at the U.S. Naval Observatory and would appear
to be valid for all years beginning with 1900:

Function USNO_Easter(anyYear As Variant) As Variant
'SOURCE:
' http://aa.usno.navy.mil/faq/docs/easter.html
'requires integer math (use \ for division, not /)
'
'Valid for years AFTER 1899
'
'c = y / 100
'n = y - 19 * (y / 19)
'k = (c - 17) / 25
'i = c - c / 4 - (c - k) / 3 + 19 * n + 15
'c = y / 100
'n = y - 19 * (y / 19)
'k = (c - 17) / 25
'i = c - c / 4 - (c - k) / 3 + 19 * n + 15
'i = i - 30 * (i / 30)
'i = i - ( i / 28 ) * ( 1 - ( i / 28 ) * ( 29 / ( i + 1 ) ) * ( ( 21 - n ) /
11 ) )
'j = y + y / 4 + i + 2 - c + c / 4
'j = j - 7 * (j / 7)
'l = i - j
'm = 3 + (l + 40) / 44
'd = l + 28 - 31 * (m / 4)
Dim c, d, n, k, i, j, l, m, y As Integer
y = anyYear
If y < 1900 Then
USNO_Easter = "Invalid prior to 1900"
Exit Function
End If
c = y \ 100
n = y - 19 * (y \ 19)
k = (c - 17) \ 25
i = c - c \ 4 - (c - k) \ 3 + 19 * n + 15
c = y \ 100
n = y - 19 * (y \ 19)
k = (c - 17) \ 25
i = c - c \ 4 - (c - k) \ 3 + 19 * n + 15
i = i - 30 * (i \ 30)
i = i - (i \ 28) * (1 - (i \ 28) * (29 \ (i + 1)) * ((21 - n) \ 11))
j = y + y \ 4 + i + 2 - c + c \ 4
j = j - 7 * (j \ 7)
l = i - j
m = 3 + (l + 40) \ 44
d = l + 28 - 31 * (m \ 4)
USNO_Easter = DateSerial(y, m, d)
End Function


"JLatham" wrote:

Could it be that the month/day sequence is different where it's not working?

I'd be interested in seeing entire the working solution. I did some VBA
code to calculate Easter and it seems to me that all I've seen points to the
solution being a bit more complex than that. Maybe I'm wrong?

Here's code for a UDF that will calculate Easter Sunday for any year after
1582.
Call it by passing a year or the address of a cell with the year in it, as
=NewDetermineEasterSunday(2007)
or
=NewDetermineEasterSunday(A1)

Function NewDetermineEasterSunday(anyYear As Variant) As Variant
'Determine Easter Sunday for any year after 1583
'INPUT: The Year
'OUTPUT: a DateSerial value that can be used to display
'the date in any format chosen by the end user
'ERROR OUTPUT: "Invalid Year" will be returned if an
'invalid input is provided.
'note, integer math (\ vs /) is critical to accurate results
'extra care has been made in places to assure integer math
'is performed thoughout the process
'
Dim anyYearValue As Variant
Dim myYear As Integer
Dim Century As Integer
Dim GoldenNumber As Integer
Dim GregorianFix As Integer
Dim ClavianFix As Integer
Dim Epact As Integer
Dim FindSundays As Integer
Dim DaysIntoMarch As Integer

anyYearValue = Val(anyYear)
If Int(anyYearValue) < anyYearValue Then
NewDetermineEasterSunday = "Invalid Year"
Exit Function
End If
If anyYearValue < 1583 Then
NewDetermineEasterSunday = "Invalid Year"
Exit Function
End If
myYear = Int(anyYearValue) ' move to working integer value
Century = (myYear \ 100) + 1
GregorianFix = ((3 * Century) \ 4) - 12
GoldenNumber = (myYear Mod 19) + 1
ClavianFix = ((8 * Century + 5) \ 25) - 5 - GregorianFix
FindSundays = ((5 * myYear) \ 4) - GregorianFix - 10 '************
Epact = Int((Int(11 * GoldenNumber) + 20 + ClavianFix)) Mod 30
If Epact < 25 Then
If GoldenNumber 11 Then
Epact = Epact + 1
End If
End If
If Epact = 24 Then
Epact = Epact + 1
End If
DaysIntoMarch = 44 - Epact
If DaysIntoMarch <= 20 Then
DaysIntoMarch = DaysIntoMarch + 30
End If
DaysIntoMarch = (DaysIntoMarch + 7) - ((DaysIntoMarch + FindSundays) Mod
7)
NewDetermineEasterSunday = DateSerial(myYear, 3, DaysIntoMarch)
End Function

Some test cases:
1943/2049: April 25
1981: April 19
1984: April 15
2285: March 22
4200: April 20
2007: April 8

"Antonio Atala" wrote:

easter day formula excel Sample Spreadsheets in spanish

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

'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6

This is what i got but does not work in spanish



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,365
Default Easter day formula

I got curious about all of this and did a lot of research and testing. The
results of the tests indicate that the formula just provided is valid for all
years from 1900 through 2033 with the exception that it gives a wrong answer
in 2079. Years after 2033 generate irregular errors. The following change
to that formula will provide uninterrupted correct results from 1900 through
2033:

=IF(A3<2079,FLOOR(DATE(A3,5,DAY(MINUTE(A3/38)/2+56)),7)-34,FLOOR(DATE(A3,5,DAY(MINUTE(A3/38)/2+57)),7)-34)

Essentially, in 2079 we have to add 1 week into the formula. References are
http://aa.usno.navy.mil/faq/docs/easter.html
and the table at
http://www.smart.net/~mmontes/freq3.html

The results of my research can be seen in this workbook at my site:
http://www.jlathamsite.com/uploads/E...omparisons.xls

Enjoy!




"Héctor Miguel" wrote:

hi, Stefi !

Do you know the formula also for date format yyyy/mm/dd? If so, please let me know it!


how about disregarding any date-order and avoid regional settings ?

- floor(date(a1,5,day(minute(a1/38)/2+56)),7)-34

hth,
hector.

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish

1) based on the 'original' winning formula in Hans W. Herber's competition [english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.




  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 434
Default Easter day formula

hi, JLatham !

I got curious about all of this and did a lot of research and testing.
The results of the tests indicate that the formula just provided is valid for all years from 1900 through 2033
with the exception that it gives a wrong answer in 2079. Years after 2033 generate irregular errors.
The following change to that formula will provide uninterrupted correct results from 1900 through 2033:
=IF(A3<2079,FLOOR(DATE(A3,5,DAY(MINUTE(A3/38)/2+56)),7)-34,FLOOR(DATE(A3,5,DAY(MINUTE(A3/38)/2+57)),7)-34)


it's a great development, thanks for sharing it :D

also, it is possible a little streamlining to the formula and avoid the 'conditional' IF function [same results]...
=floor(date(a3,5,day(minute(a3/38)/2+56+(a3=2079))),7)-34

my US $0.02
regards,
hector.

Essentially, in 2079 we have to add 1 week into the formula. References are
http://aa.usno.navy.mil/faq/docs/easter.html
and the table at
http://www.smart.net/~mmontes/freq3.html

The results of my research can be seen in this workbook at my site:
http://www.jlathamsite.com/uploads/E...omparisons.xls

Enjoy!



  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Easter day formula

forgot to mention you need to put the years in column a and this formula in
column b and copy down

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
couldn't you just use this and format the date the way you want it?

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

--


Gary


"Stefi" wrote in message
...
Hi Hector,

Do you know the formula also for date format yyyy/mm/dd? If so, please let
me know it!
Thanks,
Stefi

"Héctor Miguel" ezt írta:

hi, Antonio !
[just in case you don't come-back to spanis-ng] :))

easter day formula Excel Sample Spreadsheets in spanish
'=FLOOR(DAY(MINUTE(IJ20/38)/2+56)&"/5/"&IJ20,7)-34
'=FRANC((JOUR(MINUTE(A1/38)/2+55)&"/4/"&A1)/7;)*7-6
This is what i have but does not work in spanish

1) based on the 'original' winning formula in Hans W. Herber's competition
[english]
- =floor(day(minute(a1/38)/2+56)&"/5/"&a1,7)-34
and assuming 'A1' holds 4 digits year for easter sunday...

2) direct translation for spanish formula is:
- =multiplo.inferior(dia(minuto(a1/38)/2+56)&"/5/"&a1,7)-34
===
[however]... you need to pay attention to 'date order'
- which depends on country / regional settings in the windows control
panel [user / pc]

3) 'original' formula is 'assuming' a date order of: dd/mm/yyyy
in 'my' case... I needed to switch that 'date order' into: mm/dd/yyyy
and the working fornula becomes to:
=multiplo.inferior("5/"&dia(minuto(a1/38)/2+56)&"/"&a1,7)-34

hth,
hector.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Easter egg in Excel 2007 ? JerryH09 Excel Discussion (Misc queries) 1 August 3rd 09 01:00 PM
Obtain Easter date? Gabbon Excel Worksheet Functions 3 April 6th 06 02:04 PM
Easter Eggs in 2003 Rock Excel Discussion (Misc queries) 2 August 24th 05 01:16 PM
Easter Eggs? Rodney New Users to Excel 1 May 10th 05 11:45 AM
Easter egg in Excel 2002? Tom Ogilvy Excel Programming 1 February 13th 04 03:29 PM


All times are GMT +1. The time now is 12:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"