Easter formula only sometimes working

[dpaton05]

I have a formula to calculate Easter for multiple years but it doesn't seem to be working for each year. This is really strange as it is the same formula except a different cell reference for the year.

G84:

=YEAR(TODAY())

G87:

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

G87 should have shown 21/4/19 but it gives the #VALUE ! error.


H84: All of the reference cells in row 84 to calculate the year have the formula of the previous column + 1 and they all appear to work correctly.
H87:

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

H87 should have shown 12/4/20 but it also gives the #VALUE error

I87:

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

I87 should be the 4/4/21 but it shows 27/6/21


For the next two columns, J and K I get the value error but in column L I get the correct date. I don't understand this as the only difference between all of the formulas in these cells is the cell reference pointing to what year it is. All of this data is stored on the sheet called 'sheet2'.

I have also uploaded a copy of the spreadsheet if you need additional information that I have not supplied.

https://www.dropbox.com/s/931p3jh0gcrdmpn/quoting tool 6.6.xlsm?dl=0


Any help would be greatly appreciated as I don't know what to do.

Thanks,
Dave

 

[i_nth]

That formula works only if your system is configured to work with US date formats.
Have a look at alternatives listed at: https://www.contextures.com/exceleastercalculation.html

 

[Michael M]

I think you are using the formula incorrectly.
If your date is in G84, by whatever means, you should be using.....

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

AND it appears your are using

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

BTW
This calculates Easter Sunday....if you want Good Friday you would need to change the figure in red to -36, Easter Monday would be -33

=FLOOR(DAY(MINUTE(G84/38)/2+56)&"/5/"&G84,7)[color=red]-36[/color]

 

[MARK858]

Please see the thread below

https://www.mrexcel.com/forum/excel-questions/389703-how-calculate-date-easter.html

 

[dpaton05]

I think you are using the formula incorrectly.
If your date is in G84, by whatever means, you should be using.....

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

AND it appears your are using

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

BTW
This calculates Easter Sunday....if you want Good Friday you would need to change the figure in red to -36, Easter Monday would be -33

=FLOOR(DAY(MINUTE(G84/38)/2+56)&"/5/"&G84,7)[COLOR=red]-36[/COLOR]

Thanks for that Michael. It is strange, the formula I was using is from Chip Peasons site, so you would think it would work.

Well the one you gave me works

 

[Michael M]

Glad to help Dave....
The link posted by @MARK858 might still be worth a read though !!

 

[Fazza]

It is strange, the formula I was using is from Chip Peasons site, so you would think it would work.

Excuse me if I missed it: did you post a link?

 

[MARK858]

Excuse me if I missed it: did you post a link?

The OP probably means this one....

http://spreadsheetpage.com/index.php/tip/calculating_easter/

 

[dpaton05]

Excuse me if I missed it: did you post a link?

http://www.cpearson.com/excel/easter.aspx

 

[Fazza]

Thank you. Looks like a m/d/yy versus d/m/yy issue.

For the formula at Chip Pearson's site, if you use US settings - can change via control panel, region & language, so that dates are m/d/yy - all should be OK.

However when on normal settings for Australia you'll get an error. Michael M has advised in post #3 a version of the formula for d/m/yy settings.

regards