Mathematically predict date of holidays, such as Easter (1 Viewer)

david.paton · Aug 13, 2018

I am trying to create a complex formula for my work. It has different rates for mon-fri, weekends and public holidays. I am trying to work out how to mathematically calculate when holidays that change each year, are to fall, such as Easter. This way, it won't need constant updating as I will be able to just code it in. Can anyone help me please?

plog · Aug 13, 2018

I didn't think it was possible up until last year. Ridders proved me wrong:

https://www.access-programmers.co.uk/forums/showthread.php?t=294630&highlight=easter

david.paton · Aug 13, 2018

I have a table that has various values in that are needed for calculations of this formula. The table has 9 columns, 3 have a metro heading, 3 have a regional heading and 3 have a remote heading. Under each of the 3 groups of headings, are 3 further sub headings of Mon-Fri, Weekend and Public holiday.

I have successfully used index and match to work out whether the locations are metro, regional or remote but is it possible to then, stay in those 3 columns and reference the date entered in another cell to work out if it is mon-fri, weekend or public holiday. This is because there are different rates that are applied to the various times.

david.paton · Aug 13, 2018

Is this called a 3D lookup?

plog · Aug 13, 2018

3 lines of code. Check the link. That's all you need.

david.paton · Aug 15, 2018

I don't really understand code so can someone help me please? This code generates the date of Easter but then I was thinking that I could paste the date to a cell. This would be done whenever the spreadsheet was opened as the date of Easter changes each year. I would then use that to generate the quotes?

Not sure if this is the right way to go about this, please steer me in the right direction if there is a better way.

isladogs · Aug 15, 2018

As my thread that plog linked in post #2 clearly explains, I adapted a formula originally written by Chip Pearson for Excel so it could be used in Access

If you want to use the formula in Excel, then use the original from Chip Pearson's site: http://www.cpearson.com/Excel/easter.aspx

But don't ask me to explain it - I just know it works

You'll have to work out the rest yourself. If necessary suggest you use a specialist Excel forum such as Mr Excel.com

david.paton · Aug 15, 2018

Using the 3 lines of code, is the date calculated and stored in the variable D? How would you then paste the value to a given cell?

Is it also possible to paste it each time the program is opened?

isladogs · Aug 15, 2018

david.paton said:
Using the 3 lines of code, is the date calculated and stored in the variable D? How would you then paste the value to a given cell?

Is it also possible to paste it each time the program is opened?

Using the Excel version you would write the formula

Code:

=EasterSunday(Year)

into the cell, replacing the Year in brackets with whatever value you want.
As with any cell in Excel, you can then paste that cell reference anywhere else
e.g =D3

david.paton · Aug 16, 2018

So let me get this straight as it doesn't seem to work for me. I tried to follow the insturctions on chip pearson's site and it gave me a massively incorrect date.

I tried this excel function from the page http://www.cpearson.com/Excel/easter.aspx:
=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34
and substituted YYYY with 2018

=FLOOR("5/"&DAY(MINUTE(2018/38)/2+56)&"/"&2018,7)-34 and it gave me the 30/9/18 for easter when easter this year was 1/4/18.

What else do I need to do to get this to give me the correct date?

isladogs · Aug 16, 2018

Your link is invalid

I adapted the last function on that page- I know that works:

Code:

Public Function EasterDate2(Yr As Integer) As Date
    Dim D As Integer
    D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    EasterDate2 = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + _
            D + (D > 48) + 1) Mod 7)
End Function

I've not tried the earlier version with FLOOR or the EasterUSNO function.
In fact I've never used the FLOOR function so can't explain it

However, AFAIK EVERYTHING on Chip's site works.
Unfortunately, Chip is no longer available to contact if you have problems as ,very sadly, he died in April 2018

Gasman · Aug 16, 2018

Not found out what you have done incorrectly yet, if anything, but if you enter 2018 in the field at the bottom of that page, his site returns 01/04/2018 ?
and if you look at the source, that is the formula, so very strange.

As ridders mentions, just use the EasterDate2 function instead, that works for me.?

Edit: I am going to hazard a guess it is due to USA date format, as the first character would be a 5 (for May) and then subtract 34 days.?
Just a wild guess, as I have the same incorrect date

In fact altering for UK would be

Code:

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

where A5 hold the year in question. That produces 01/04/2018 and correct 16/04/2017 for last year.

Still have no clue to how it works though.

HTH

david.paton said:
So let me get this straight as it doesn't seem to work for me. I tried to follow the insturctions on chip pearson's site and it gave me a massively incorrect date.

I tried this excel function from the page http://www.cpearson.com/Excel/easter.aspx:
=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34
and substituted YYYY with 2018

=FLOOR("5/"&DAY(MINUTE(2018/38)/2+56)&"/"&2018,7)-34 and it gave me the 30/9/18 for easter when easter this year was 1/4/18.

What else do I need to do to get this to give me the correct date?

david.paton · Aug 17, 2018

Gasman said:
Code:

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

HTH

This appears to work. I now have a list of holidays for Australia and I have a row at the top of them that contains the current year. It needs to reference a cell, A5 in your example. I am using I30 in my spreadsheet and I was thinking I could add some code that would enter the current year into I30 whenever the spreadsheet is opened. Could someone help me with some code to do this please?

Is this a good way to do this? Is there a better way to do this, such as add something into the function in place of the A5 or I30 (in my spreadsheet) that will produce the current year?

david.paton · Aug 17, 2018

Figured it out. I used =FLOOR(DAY(MINUTE(I30/38)/2+56)&"/05/"&I30,7)-34 where the year is in I30. I have a cell with the current date in it: I29=NOW() and a cell that works out the year from that date: I30=YEAR(I29).

isladogs · Aug 17, 2018

david.paton said:
Figured it out. I used =FLOOR(DAY(MINUTE(I30/38)/2+56)&"/05/"&I30,7)-34 where the year is in I30. I have a cell with the current date in it: I29=NOW() and a cell that works out the year from that date: I30=YEAR(I29).

Congratulations. You'll be telling us next you understand the formula

If so, you're one up on me!

Gasman · Aug 17, 2018

Well I broke it down into it's component parts (thank God for BODMAS

) to see why it was giving the incorrect date, then spotted the 5 at the start of the formula.
But still have no idea as to why 38, 56 or 34 are the required values.:banghead:

isladogs · Aug 17, 2018

Gasman said:
Well I broke it down into it's component parts (thank God for BODMAS ) to see why it was giving the incorrect date, then spotted the 5 at the start of the formula.
But still have no idea as to why 38, 56 or 34 are the required values.:banghead:

My point precisely

Chip really was a very clever programmer. Very sad that he's no longer with us.
I hope his website remains online for many years as it's a great resource.

Gasman · Aug 17, 2018

ridders said:
I hope his website remains online for many years as it's a great resource.

Sadly, I cannot see that happening. Eventually the site will come up for renewal.
That is 2019-06-22.

david.paton · Aug 17, 2018

The list of public holidays is almost created, I only have one bit left to make. Any ideas how I can do this as I think I am now over thinking things. I have various public holidays that are on a set day, such as new years day 1,1,xxxx or christmas day 25,12,xxxx. How do I input the current year where the xxxx is?

Gasman · Aug 17, 2018

DateSerial ?

https://docs.microsoft.com/en-us/of...rence/User-Interface-Help/dateserial-function

TBH I just look up the holidays and add them to my table periodically. Job done.

Mathematically predict date of holidays, such as Easter (1 Viewer)

Registered User.

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Enthusiastic Amateur

Registered User.

Registered User.

MVP / VIP

Enthusiastic Amateur

MVP / VIP

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread