Answered by:
working with dates !
Question

Hey Guys,
Here is the scenario. I have data in column A which is due on specifics dates (values in column B) in a month. Say item in cell A1 is due on T+1, where value of T will be given by the users. So for example if T = 06/30/2012 then output in column C should be 07/01/2012, given cell B1 is having "T+1". Something like this..
Item T+ Actual Date [T = 6/30/2012] Flag1 T+9 7/9/2012 My question is how to code this thing. I tried to record a macro but it is not yielding the desired output.
Friday, July 13, 2012 8:30 PM
Answers

Hi,
you can try with WORKDAY function. This function will add specified days excluding weekends and any dates identified as holidays. In column A is a start date, in column B users can put any number of days, in table in column E you can add holidays (which will also be excluded) and in column C you'll have a result.
Hope this will help,
M
 Edited by Martina Matovina Monday, July 16, 2012 1:10 PM I have entered a wrong column (F instead of E)
 Proposed as answer by Hans Vogelaar MVPMVP Monday, July 16, 2012 1:13 PM
 Marked as answer by Palash Saha Tuesday, July 17, 2012 11:01 AM
Monday, July 16, 2012 1:10 PM 
Martina's suggestion should do what you want.
Please note that she uses a continental European system, so in her formula, the semicolon ; acts as list separator.
On a system where the comma is the list separator, the formula for C2 would become
=WORKDAY(A2,B2,$E$2:$E$7)
If you assign the name Holidays to the range E2:E7 (or wherever your list of holidays is stored), you can use
=WORKDAY(A2,B2,Holidays)
Regards, Hans Vogelaar
 Marked as answer by Palash Saha Tuesday, July 17, 2012 11:01 AM
Monday, July 16, 2012 1:17 PM
All replies

Why not simply have a number such as 1 or 9 in column B, and a formula such as =IF(A2="","",A2+B2) in column C?
You can even apply the custom number format "T+"0 to column B, so that the number 9 will be displayed as T+9.
Regards, Hans Vogelaar
Friday, July 13, 2012 8:58 PM 
Hey Hans,
Thanks for the reply. I tried this. This can work. It was my bad that I did not mention the whole scenario. The whole thing should also take into concideration "Saturday" & "Sunday" & / or if there is any publice holiday. So in my expample say 07/09/2012 is "Saturday" then the output should be next available working day i.e. "Monday" i.e. 07/11/2012. Now if the "Monday" is also a public holiday then it should be "Tuesday" i.e. 07/12/2012.
I am actually getting stuck here. I have a Table containing all the public holidays. So I can use lookup to find whether it is a public holiday or not. But I am unable to make it for Saturdays & Sundays.
Thanks
Palash
Monday, July 16, 2012 12:16 PM 
Hi,
you can try with WORKDAY function. This function will add specified days excluding weekends and any dates identified as holidays. In column A is a start date, in column B users can put any number of days, in table in column E you can add holidays (which will also be excluded) and in column C you'll have a result.
Hope this will help,
M
 Edited by Martina Matovina Monday, July 16, 2012 1:10 PM I have entered a wrong column (F instead of E)
 Proposed as answer by Hans Vogelaar MVPMVP Monday, July 16, 2012 1:13 PM
 Marked as answer by Palash Saha Tuesday, July 17, 2012 11:01 AM
Monday, July 16, 2012 1:10 PM 
Martina's suggestion should do what you want.
Please note that she uses a continental European system, so in her formula, the semicolon ; acts as list separator.
On a system where the comma is the list separator, the formula for C2 would become
=WORKDAY(A2,B2,$E$2:$E$7)
If you assign the name Holidays to the range E2:E7 (or wherever your list of holidays is stored), you can use
=WORKDAY(A2,B2,Holidays)
Regards, Hans Vogelaar
 Marked as answer by Palash Saha Tuesday, July 17, 2012 11:01 AM
Monday, July 16, 2012 1:17 PM 
Martina & Hans,
Thanks a lot guys. It worked.
Thanks
Palash
Tuesday, July 17, 2012 11:02 AM