Solved Function for calculating holidays .... (1 Viewer)

[MarionD]

I want to print an attendance register for a certain Period e.g. 01.04.2021 till 30.04.2021 where the backcolour of fields for weekends and holidays are grey.
Using the weekday function works fine for weekends, but how do I calculate moveable holidays, e.g. Easter Monday, Good Friday. Something like check if (thisdate) is a holiday??
Any help would be most appreciated.
Thanks

 

[Isaac]

You can create a table and populate it with holiday dates.

That's the thing with holidays - there is no magic, one-size-fits-all interpretation of what a Holiday is. It's not like there is an ANSI Holidays entity

Holidays mean totally different things depending on whether the employment context is Walmart, Hilton, Bank of America, or somewhere in California, Dubai, Oslo or India.

It's defined by your business, so you might as well take the time to populate a table with the holidays you want - then you'll be able to design whatever you want to take those into account

 

[Ranman256]

I make a table tHolidays. Then use it to calculate days by removing those workdays if listed in the tHoliday table.
12/25/2021 , Xmas
1/1/2021, New Years
etc.

 

[MarionD]

Hi there, yes I have a table with the holidays I want to use. The user then clicks "yes or no" next to the holiday, but as Easter Monday, Good Friday, Ascencion Day for example don't fall on the same day every year, I am having a problem with them. If the user has clicked "yes" in the tbl_Holidays next to "easter Monday" and the month I am printing is say, April - then I want to check if Date1 (01.04.2021) is a Holiday, looping through the days of the month.
The use will be very local in Germany, so I don't need to consider any other Holidays than those recognised here.

 

[Gasman]

I just used to add the entries for the next year manually?

 

[isladogs]

I have a function which calculates Easter Sunday for any year from which you can easily get Good Friday and Easter Monday.
See Easter Calculator - Mendip Data Systems
This avoids the need to do any manual entries

 

[MarionD]

Wow! Thank you so much!

I now have a diefferent Problem...Using this and the other Functions I copy all the dates into a local Table... and try th check from there if the Date is in.
_____________________________________________________________________________________________________________
Dat = Forms!frm_anwesenheitsliste_drucken!vondatum
Enddate = DateAdd("m", 1, Dat) - 1
Nodays = DateDiff("d", Dat, Enddate)

For I = 1 To Nodays
FTDays.FindFirst "FTDate=" & Format(Dat, "mm-dd-yyyy")
If FTDays.NoMatch Then
Me.Controls(CStr(I)).BackColor = vbWhite 'if the date is not the table the back colour should be white
Else
Me.Controls(CStr(I)).BackColor = 12632256 'if the date IS the table the back colour should be grey
End If
x = Weekday(Dat, vbMonday)
If x = 6 Or x = 7 Then
Me.Controls(CStr(I)).BackColor = 12632256
End If

Dat = DateAdd("d", 1, Dat)
Next I
________________________________________________________________________________________________________
My Problem is that even though the date is in the Table the .NoMatch ALWAYS retuns true?

 

[arnelgp]

you add # as delimiter to your date and use the English format:

FTDays.FindFirst "FTDate=" & Format$(Dat, "\#mm\/dd\/yyyy\#")

 

[MarionD]

Thank you all sooo much!
Arnelgp - it worked ! It can be so easy if you know what to do!!
Isladogs - that Function is the best thing since ketchup!!

Thanks again

 

[isladogs]

You're welcome. It was originally done for Excel by Chip Pearson. I converted it for Access.
I have similar but much simpler functions to determine UK Bank Holiday dates but no use for you in Germany.

 

[arnelgp]

I converted it for Access.

I saw the orig code and they are same, no conversion done.

 

[isladogs]

I saw the orig code and they are same, no conversion done.

Yes I know. There's a very simple reason for that!

Back in 2008 or so, Chip provided another function which didn't work in Access
I sent Chip my modified version which works in both Access & Excel which he used on his website in place of the original.

Are you happy now?

 

[arnelgp]

Are you happy now?

so nobody can attest the veracity of your claim?
the only fact that you have the attribution on your website that
you got it from mr.pearson and not the other way around?

 

[isladogs]

@arnelgp
Your suspicions are a sad reflection on you.
If I had wanted to claim credit without justification, I wouldn't have have given a link to the original source material.
I always acknowledge any website or author whose ideas I use or adapt.

 

[arnelgp]

Your suspicions are a sad reflection on you.

Because you are my idol, haha

 

[isladogs]

No further comment