Calculate due date depending on type of request

[anjali0729]

Hi All,

I need to calculate business due date where it differs from request
Ex:
Received Date Due date Request type TAT
4/1/2013 4/4/2013 Normal 3 day
4/1/2013 4/2/2013 Rush 1 day
4/1/2013 4/6/2013 Exception 5 day

The due date should atumatically calculate including the weekends which is sat and sunday.

Pls help me with excel formula...

[Tony Valko]

It looks like all you need to do is add the number of days to the date.

A1 = 4/1/2013
D1 = 3

=A1+D1

[wenqq3]

HI anjali0729
if you column is 3 day, then you need like this

Please Login or Register  to view this content.

and change the cell format to date.

[anjali0729]

Thanks for your prompt responce....i need the due date calculate automatically depending on the request type as each type has different TAT...

Pls help again.....

Thanks,
Hema

[Tony Valko]

Maybe this...

Create a 2 column table with the request type in the left column and the TAT (whatever that is, turn around time?) in the right column:

Normal.........3
Rush............1
Exception.....5

Let's assume that table is in the range J2:K4.

Then:

A2 = some date like 4/1/2013
C2 = some request type like Normal

Then, the formula would be:

=A2+VLOOKUP(C2,J$2:K$4,2,0)

Format as Date

[anjali0729]

hey Tony..

Thank u so much..this does work.....one more thing its capturing the weekends..is it possible to not count the weekend while applying the formula mentioned by.....

Thanks again for teaching me sumthing new...

Thanks,
anjali

[Tony Valko]

Something like this...

=WORKDAY(A2,VLOOKUP(C2,J$2:K$4,2,0))

If you need to exclude any holidays from the calculation then list the holiday dates in a range of cells, say L2:L10, then use this version:

=WORKDAY(A2,VLOOKUP(C2,J$2:K$4,2,0),L2:L10)

Format the result as Date

[anjali0729]

Hi Tony,......

Need one more help....the vlook up is aboslutely working..again i have another creiteria..where the vlook up need to search for normal or rush as said above and them look in next column for exception..is there way out for this......

pls help ....

Thanks a lot

[akhileshgs]

I think better to have if formula then.. Can you post a sample file?

[Tony Valko]

Not sure I understand.

the vlook up need to search for normal or rush

Isn't that what the VLOOKUP is already doing?

[anjali0729]

hey Tony..

The Vlook up is doing perfectly, however the exceptions are in onother column. so when formula seaches it finds only normal, rush and exceptions where only we update type i e Rush n normal in 1 column but the exceptions r in onther column which v look cannot identify...

[Tony Valko]

Sorry, I don't understand.

Can you post a SMALL sample file (about 20 rows worth of data is plenty) so I see how your data is laid out?