Home Forum Contact

 Access World Forums [SOLVED] Mathematically predict date of holidays, such as Easter
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

 08-16-2018, 11:25 PM #16 Gasman Access newbie   Join Date: Sep 2011 Location: Swansea, South Wales,UK Posts: 2,138 Thanks: 269 Thanked 318 Times in 303 Posts Re: Mathematically predict date of holidays, such as Easter 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. __________________ Access novice. Sometimes trying to give something back. Access 2007
08-16-2018, 11:33 PM   #17
ridders
Part time moderator

Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,805
Thanks: 79
Thanked 1,428 Times in 1,333 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 Originally Posted by Gasman 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.
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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est

08-17-2018, 12:23 AM   #18
Gasman
Access newbie

Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,138
Thanks: 269
Thanked 318 Times in 303 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 Originally Posted by ridders 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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 08-17-2018 at 12:42 AM.

 08-17-2018, 03:28 AM #19 david.paton Newly Registered User   Join Date: Jun 2013 Location: NSW, Australia Posts: 328 Thanks: 55 Thanked 0 Times in 0 Posts Re: Mathematically predict date of holidays, such as Easter 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?
 08-17-2018, 03:31 AM #20 Gasman Access newbie   Join Date: Sep 2011 Location: Swansea, South Wales,UK Posts: 2,138 Thanks: 269 Thanked 318 Times in 303 Posts Re: Mathematically predict date of holidays, such as Easter DateSerial ? https://docs.microsoft.com/en-us/off...erial-function TBH I just look up the holidays and add them to my table periodically. Job done. __________________ Access novice. Sometimes trying to give something back. Access 2007
08-17-2018, 03:37 AM   #21
ridders
Part time moderator

Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,805
Thanks: 79
Thanked 1,428 Times in 1,333 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 Originally Posted by Gasman Sadly, I cannot see that happening. Eventually the site will come up for renewal. That is 2019-06-22.
What often happens in such cases is that other people offer to continue hosting the site
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est

 08-17-2018, 03:43 AM #22 david.paton Newly Registered User   Join Date: Jun 2013 Location: NSW, Australia Posts: 328 Thanks: 55 Thanked 0 Times in 0 Posts Re: Mathematically predict date of holidays, such as Easter I just found out how to do it, I used the date formula =DATE(I30,1,1), where I30 is the year, then the month and day of month, for instance, the above example of new years day.
08-17-2018, 03:46 AM   #23
Gasman
Access newbie

Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,138
Thanks: 269
Thanked 318 Times in 303 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 Originally Posted by ridders What often happens in such cases is that other people offer to continue hosting the site
That crossed my mind, but somewhere where it could be accessed easily by whoever inherited it.
The problem I would have thought is getting all the pages etc. It appears to have been created in Visual Studio and ASP, none of which I am familiar with.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

08-17-2018, 03:46 AM   #24
MajP
Newly Registered User

Join Date: May 2018
Posts: 294
Thanks: 3
Thanked 73 Times in 71 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 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?
This is how I do it in my calendar control with US holidays. A lot of our holidays have rules like the first monday of the second week or last monday of the 5th month. Hence the function DayOfNthWeek

Code:
```Public Enum HolidayName
holNew_Years = 1
holML_King_BDay = 2
holPresidents_Day = 3
holEaster = 4
holMemorial_Day = 5
holIndependance_Day = 6
holLabor_Day = 7
holColumbus_Day = 8
holVeterans_Day = 9
holThanksgiving = 10
holChristmas = 11
End Enum

Public Function GetHoliday(ByVal TheYear As Long, TheHolidayName As HolidayName) As Date
Dim intWeekDay As Integer
Dim intDay As Integer
Dim intMonth As Integer
'New Years Day
Select Case TheHolidayName
Case holNew_Years
GetHoliday = DateSerial(TheYear, 1, 1)
Case holML_King_BDay
'3rd monday of January
GetHoliday = DayOfNthWeek(TheYear, 1, 3, vbMonday)
Case holPresidents_Day
'Presidents Day  3rd Monday of Feb
GetHoliday = DayOfNthWeek(TheYear, 2, 3, vbMonday)
Case holMemorial_Day
GetHoliday = LastMondayInMonth(TheYear, 5)
Case holIndependance_Day
GetHoliday = DateSerial(TheYear, 7, 4)
Case holLabor_Day
GetHoliday = DayOfNthWeek(TheYear, 9, 1, vbMonday)
Case holColumbus_Day
GetHoliday = DayOfNthWeek(TheYear, 10, 2, vbMonday)
Case holVeterans_Day
' Veteranss Day
' Although originally scheduled for celebration on November 11,
' starting in 1971 Veterans Day was moved to the fourth Monday of October.
' In 1978 it was moved back to its original celebration on November 11.
GetHoliday = DateSerial(TheYear, 11, 11)
Case holThanksgiving
GetHoliday = DayOfNthWeek(TheYear, 11, 4, vbThursday)
Case holChristmas
GetHoliday = DateSerial(TheYear, 12, 25)
Case holEaster
'Not US Federal Holiday
GetHoliday = EasterUSNO(TheYear)
End Select

End Function

Public Function DayOfNthWeek(intYear As Long, intMonth As Integer, N As Integer, vbDayOfWeek As Integer) As Date
'Thanksgiving is the 4th thursday in November(11)
'dayOfNthWeek(theYear,11,4,vbThursday)
DayOfNthWeek = DateSerial(intYear, intMonth, (8 - Weekday(DateSerial(intYear, intMonth, 1), _
(vbDayOfWeek + 1) Mod 8)) + ((N - 1) * 7))
End Function

Function LastMondayInMonth(intYear As Long, intMonth As Long) As Date
'Used for memorial day
Dim LastDay As Date
'define last day of the month of interest:
LastDay = DateSerial(intYear, intMonth + 1, 0)
'use to get last monday:
LastMondayInMonth = LastDay - Weekday(LastDay, vbMonday) + 1
End Function

Public Function EasterUSNO(YYYY As Long) As Long
Dim C As Long
Dim N As Long
Dim K As Long
Dim I As Long
Dim J As Long
Dim L As Long
Dim M As Long
Dim D As Long

C = YYYY \ 100
N = YYYY - 19 * (YYYY \ 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 = YYYY + YYYY \ 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)
EasterUSNO = DateSerial(YYYY, M, D)
End Function```
And you use it like
Code:
`somefield = getHoliday(2018,holMemorial_Day)`

08-17-2018, 03:48 AM   #25
Gasman
Access newbie

Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,138
Thanks: 269
Thanked 318 Times in 303 Posts
Re: Mathematically predict date of holidays, such as Easter

Quote:
 Originally Posted by david.paton I just found out how to do it, I used the date formula =DATE(I30,1,1), where I30 is the year, then the month and day of month, for instance, the above example of new years day.
Sorry, I had my Access hat on and a blonde moment.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post Demetious Forms 3 02-10-2011 07:58 AM jordi20 Queries 2 07-02-2010 03:08 AM kigor Queries 3 04-24-2010 11:44 AM Nittylions Modules & VBA 4 01-26-2009 12:33 PM alex_aqui General 2 11-06-2002 05:19 AM

All times are GMT -8. The time now is 04:57 PM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Sponsored Links How to advertise Media Kit