Date Calculation

[Secret Squirrel]

I need to calculate a date in my query based on the hiredate of my employees.
I need to add 7 months to their hiredate but I need it to calculate it to the
first business day of the 7th month. For example if an employee was hired on
3/15/08 then I need the result to be 10/1/08

 

[John W. Vinson]

I need to calculate a date in my query based on the hiredate of my employees.
I need to add 7 months to their hiredate but I need it to calculate it to the
first business day of the 7th month. For example if an employee was hired on
3/15/08 then I need the result to be 10/1/08

Do you have a table of business days, or (contrariwise) a table of holidays?
You can get the first day of the 7th month easily:

DateSerial(Year([hiredate]), Month([hiredate]) + 7, 1)

You might need a bit of VBA code to detect when this day falls on a weekend or
holiday, though.

 

[Secret Squirrel]

I do have a table of holidays, "tblHolidays". I would need to utlilize that
so the date doesn't fall on a holiday. How would I add that to the formula?

I need to calculate a date in my query based on the hiredate of my employees.
I need to add 7 months to their hiredate but I need it to calculate it to the
first business day of the 7th month. For example if an employee was hired on
3/15/08 then I need the result to be 10/1/08

Do you have a table of business days, or (contrariwise) a table of holidays?
You can get the first day of the 7th month easily:

DateSerial(Year([hiredate]), Month([hiredate]) + 7, 1)

You might need a bit of VBA code to detect when this day falls on a weekend or
holiday, though.