Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2018, 11:25 PM   #16
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
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
Gasman is offline   Reply With Quote
Old 08-16-2018, 11:33 PM   #17
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Mathematically predict date of holidays, such as Easter

Quote:
Originally Posted by Gasman View Post
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.

Website links:
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
Previously known as ridders : 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.
isladogs is offline   Reply With Quote
Old 08-17-2018, 12:23 AM   #18
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Mathematically predict date of holidays, such as Easter

Quote:
Originally Posted by ridders View Post
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.
Gasman is offline   Reply With Quote
Old 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
david.paton is on a distinguished road
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?
david.paton is offline   Reply With Quote
Old 08-17-2018, 03:31 AM   #20
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
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
Gasman is offline   Reply With Quote
Old 08-17-2018, 03:37 AM   #21
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Mathematically predict date of holidays, such as Easter

Quote:
Originally Posted by Gasman View Post
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.

Website links:
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
Previously known as ridders : 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.
isladogs is offline   Reply With Quote
Old 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
david.paton is on a distinguished road
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.

david.paton is offline   Reply With Quote
Old 08-17-2018, 03:46 AM   #23
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Mathematically predict date of holidays, such as Easter

Quote:
Originally Posted by ridders View Post
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
Gasman is offline   Reply With Quote
Old 08-17-2018, 03:46 AM   #24
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 604
Thanks: 8
Thanked 138 Times in 136 Posts
MajP will become famous soon enough
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)
MajP is offline   Reply With Quote
Old 08-17-2018, 03:48 AM   #25
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Mathematically predict date of holidays, such as Easter

Quote:
Originally Posted by david.paton View Post
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
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Phrase Predict? Demetious Forms 3 02-10-2011 07:58 AM
My fitler fields used to predict entries and now they don't jordi20 Queries 2 07-02-2010 03:08 AM
Number of Holidays between two date kigor Queries 3 04-24-2010 11:44 AM
Removes Holidays from Date Diff Cal. Nittylions Modules & VBA 4 01-26-2009 12:33 PM
Date + x to avoid holidays? alex_aqui General 2 11-06-2002 05:19 AM




All times are GMT -8. The time now is 04:12 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


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World