Date Calculation

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

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
 
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
Click to expand...

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.
 
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?

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Back
Top