Automation to calculate the SLA date?

[Raja]

Hi All,

In the sheet1 column G i have job codes like G4 and G5 and in th
Column H i have Appointmrnt dates coming up like in th
format15-Jul-09.

Now i need a SLA calculation in I column

Example

IF CELL G2 = G4 THEN ITS SHOULD ADD CELL H2+6 IN CELL I2 THAT I
15-JUL-09+6 DAYS
IF CELL G2 = G5 THEN ITS SHOULD ADD CELL H2+1IN CELL I2 THAT I
15-JUL-09+1DA
*SLA for the Code G4 is D+6 & Code G5 is D+1; D= What ever date tha
appears in cells of Column H.*
I know this can be done easily with the VBA script but the importan
thing is the **macro should exclude the Holidays like sunday and all th
banking holidays of UK thats occurs in between the dates.**
Example :

*Say cell G2 = G4, H2 = 15-jul-09
since the SLA for G4 = D+6days
Cell I2 should show 22-jul-09 since 18-jul-09 is sunday same like thi
its should exclude banking holidays also.*

Any help in this one will be appreciated and hats off for them.

Regards,

Raj

 

[Matthew Herbert]

Hi All,

In the sheet1 column G i have job codes like G4 and G5 and in the
Column H i have Appointmrnt dates coming up like in the
format15-Jul-09.

Now i need a SLA calculation in I column

Example

IF CELL G2 = G4 THEN ITS SHOULD ADD CELL H2+6 IN CELL I2 THAT IS
15-JUL-09+6 DAYS
IF CELL G2 = G5 THEN ITS SHOULD ADD CELL H2+1IN CELL I2 THAT IS
15-JUL-09+1DAY
*SLA for the Code G4 is D+6 & Code G5 is D+1; D= What ever date that
appears in cells of Column H.*
I know this can be done easily with the VBA script but the important
thing is the **macro should exclude the Holidays like sunday and all the
banking holidays of UK thats occurs in between the dates.**
Example :

*Say cell G2 = G4, H2 = 15-jul-09
since the SLA for G4 = D+6days
Cell I2 should show 22-jul-09 since 18-jul-09 is sunday same like this
its should exclude banking holidays also.*

Any help in this one will be appreciated and hats off for them.

Regards,

Raja

Raja,

I haven't had to deal with holidays or UK banking days before, but you
should check out formulas available to you through the function dialog
box. Simply filter the formulas by Date & Time. Pay attention to
DATE (to add years, months, or days), WEEKDAYS (to get the day of the
week that the date falls on), and NETWORKDAYS (to get the number of
workdays between two dates). The help documentation for these
formulas is good.

For example, you can add 6 days by doing the following (assuming there
is a valid date in A1):

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+6)

If these formulas don't fit the bill, then check out Chip's site:
http://www.cpearson.com/Excel/betternetworkdays.aspx

Best,

Matthew Herbert