locked
working with dates ! RRS feed

  • 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 semi-colon ; 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 semi-colon ; 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