Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to calculate the SLA date?
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 ------------------------------------------------------------------------ Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116531 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to calculate the SLA date?
On Jul 15, 10:04*pm, Raja wrote:
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 ------------------------------------------------------------------------ Raja's Profile:http://www.thecodecage.com/forumz/member.php?userid=497 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=116531 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to calculate end date using order date and lead time variab | Excel Worksheet Functions | |||
stop automation to date field | Excel Discussion (Misc queries) | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
Excel 2003 does not calculate automation add-in functions automati | Excel Programming | |||
Feeding Date Values From C++ Using Automation | Excel Programming |