Date Calculation

  • Thread starter Secret Squirrel
  • 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
 
J

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

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?

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top