Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easter egg in Excel 2007 ? | Excel Discussion (Misc queries) | |||
Obtain Easter date? | Excel Worksheet Functions | |||
Easter Eggs in 2003 | Excel Discussion (Misc queries) | |||
Easter Eggs? | New Users to Excel | |||
Easter egg in Excel 2002? | Excel Programming |