Sample Date Functions

[Pat Hartman]

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is:

1. Business days between two dates.
2. Business day + days = next business date
3. Easter (Western)
4. Age
5. First Day of Quarter
6. First Day of Week
7. First Day of Month
8. First Day of Previous Month
9. First Day of Next Month
10. Last Day of Quarter
11. Last Day of Week
12. Last Day of Month
13. Last Day of Previous Month
14. Last Day of Next Month
15. First Weekday (sun, mon, etc) following specified date.
16. Special DateDiff function that separates the answer into parts

The Business days function includes a holiday table which allows you to specify holidays that do not fall on weekend days.

Updated to include an A2003 version of the app. The original version was created with A2010 and for some reason cannot be opened with A2007 so use the A2003 version if you have A2007.

I added a newer version to the list. Go to the end of the thread to get the latest version.

 

[JANR]

Great stuff, one suggestion thou is to modify this:

Private Sub cmbFunction_AfterUpdate()
Me.txtReqDate = Eval(Me.cmbFunction & "(" & Format(Me.txtBaseDate, "\#mm\/dd\/yyyy\#") & ")")
End Sub

so that it works internationally

JR

 

[Pat Hartman]

Thanks. I'll take a look at it.

 

[Pat Hartman]

I added a new updated version and deleted the old versions. I have a very old version that is A2003. PM me if you want it. It doesn't have all the functions the newest version has but it may have what you need.

 

[Pat Hartman]

I added a new table to hold rules for calculating holiday dates as well as code to generate holiday dates for a given year along with some documentation. The calculations cater to US holidays but will work for any scheme where holidays are calculated using the same rules. I did come across one Canadian holiday that is the "next to last" week of a month. My type #2 code handles weeks 1-5 or Last. "NextToLast" would need to be added to the code. Any custom calculation such as Easter (one version included) or Ramadan needs to be specifically coded. I didn't test all 14 cases (week starting on 1-7 plus standard and leap years so please let me know if you find any error. I also didn't accommodate non-US date formats. Changing the display to the custom dd-mmm-yyyy might accommodate both the US and Europe.