PDA

View Full Version : Exclude Sat & Sun while calcualting due date



rajagopal
04-11-2011, 04:58 AM
I want to calculate TAT status (in column H) based on Due date (column E) & Actual sent date (column G)

Due date have to be calculated based on 3 different entries (Active only, Passive only, Active & Passive only) in column C

While calculating due date, we've to exclude Sat & Sun.

All entries are in MM/DD/YY hh:mm format.

Please find attached and help with the formula.


Based on the entry in column C (Active only / Passive only / Active & Passive only), TAT SLA will vary and hence the TAT status should show as "Met" if it is within the SLA. You've to exclude Sat & Sun)

tilamok
04-11-2011, 05:19 AM
if you have a date in cell E1
then the formula
text(e1,"ddd") will give you the the 1st 3 letters of the weekday.

You can use this to identify if it's a weekday.

rajagopal
04-11-2011, 05:28 AM
Due date is to be calculated by a straight formula considering sat & sun. Can you please tell me on this?

tilamok
04-11-2011, 07:33 AM
if due date is in cell e1
and you want to move 2 days forward if its a "sat" or 1 day if "Sun then

New due date =
e1 + if(text(e1,"ddd")="sat",2,0)+if(text(e1,"ddd")="sun",1,0)

shrivallabha
04-11-2011, 07:46 AM
Hi, following formula should work as I've considered case "active only" as differentiating parameter (48 hrs) and rest of the cases are 72 hrs. Place the formula in cell H2 as:

=IF(E2>G2,"Met",IF(C2="Active only",IF(E2+2+(IF(WEEKDAY(E2)>=4,(WEEKDAY(E2)-3),0))>G2,"Met","Not Met"),IF(E2+3+(IF(WEEKDAY(E2)>=3,(WEEKDAY(E2)-2),0))>G2,"Met","Not Met")))

Kindly check it against some more cases and then post back if you find any discrepancies.

BrianMH
04-11-2011, 12:24 PM
Want to be able to define more SLAs? Want to skip holidays?
Well I was bored and did a really long formula with named ranges. I have attached the spreadsheet. Have a look.

rajagopal
04-11-2011, 11:28 PM
It is not showing correct results. for #1, work should be completed within 4/9/11 2:21 PM whereas actual sent date was 4/12/11 2:10 PM - TAT not met but the formula shows Met.

It should consider timing as well.

BrianMH
04-11-2011, 11:33 PM
When you say it should consider timing what do you mean exactly?

rajagopal
04-12-2011, 12:44 AM
In Date of receipt, Actual sent date, you've the

rajagopal
04-12-2011, 12:46 AM
In Date of receipt, Actual sent date, you've the date given as 4/7/11 2:21 PM

From this time, 48 hrs or 72 hrs as per type of work

shrivallabha
04-12-2011, 12:46 AM
After re-reading this thread, I still have doubt about the entry#1. I am attaching the file to which I have added a column (Green cells). See if the due dates calculated by the formula are correct or not. If they are correct then:
1. The formula can then be shifted to Due Date Column.
2. Then it will be a simple comparison between column E and G.

I have highlighted the doubt for #1 in the attached workbook and your post at #7 is all the more baffling. Have a look at the attached file.

rajagopal
04-12-2011, 12:53 AM
Hi,
Thanks man. I got it.