Date Functions

[H Pepper]

My task is to create a capacity forecasting database that can calculate how many patients a doctor is likely to see in any given time period. A user will be able to define an event and say the occurrence of that events, ie

Clinic A occurs every Tuesday.
Clinic B occurs last Friday of month

The user can then say how many patients are likely to be seen, ie 10 or 15.

I need a forumula to work out "every Tuesday", "last Friday of the month". Most of the date functions I see require the user to input a date but in this case they are not

Once I know how many "every Tuesdays" there are in the user's given time-frame, I can work out the number of patients, ie
52 Tuesdays @ 10 patients = 520 patients.

Can anyone help or point me in the right direction?

 

[Pat Hartman]

Download the sample usefuldatefunctions database. It has a function that finds the first instance of a given weekday for a given month. You can then find subsequent instances by adding 7 to the returned date.
Useful Date Functions