Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Problem calculating dates

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hello,


    I'm having a little problem calculating dates.

    In a worksheet, I defined three tabs "Sent Letters", "Variables" and "Bank Holidays".

    The following information is stored in the tab "Variables".

    (A1) Letter (B1) Days to wait
    (A2) 8 (B2) 20
    (A3) 10 (B3) 30
    (A4) 12 (B4) 40
    (A5) 14 (B5) 50


    The following information is stored in the tab "Sent Letters".

    (A1) Letter (B1) Date Sent (C1) New Date
    (A2) 8 (B2) 1-1-2002 (C2) (Formula)
    (A3) .... (B3) .... (C3) (Formula)
    (A4) .... (B4) .... (C4) (Formula)
    (A5) .... (B5) .... (C5) (Formula)

    In the tab "Bank Holidays" there are a number of dates of bank holidays, like
    Easter and Christmas.

    These dates are stored in the cells A1 to A20.

    All entries are made in the tab "Sent Letters".

    When a number is entered in cell A2, (for example "8") and a date
    (for example "January 1st 2002") in cell B2, I want the following actions
    to be done.

    First the "Days to wait" are to be found (20 days) using the VLOOKUP function
    on the tab "Variables".

    The value found there has to be added to the date in cell B2 (on the tab "Sent Letters"),
    and the result (January 21st 2002) has to be stored in cell C2.

    No problem so far....

    But of course there are a couple of weekends (Saturday's and Sunday's) "stored" in
    these 20 days.

    I do not want these weekends to be "counted". I only need "Working" days (Monday through Friday)
    to be counted.

    To make it even more complicated, there could also be some bank holidays (like Easter and Christmas)
    "stored" in these "Days to wait".

    All known holidays are stored in the tab "Holidays" in the same worksheet.

    So what I want to do is the following.
    When a "Letter Number" and a "Date Sent" is entered, the corresponding "Days to wait" has
    to be found using the VLOOKUP function on the tab "Variables".

    If there are weekends or holidays in the "Days to wait", they may not be "counted".

    Could anyone please tell me if this is possible using "formulas" only ?
    (I'm not a VBA programmer.)

    Thanks in advance and best regards,




    Frits Jager
    (The Netherlands)


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,006
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-03 05:07, Flash0220 wrote:

    Hello,


    I'm having a little problem calculating dates.

    In a worksheet, I defined three tabs "Sent Letters", "Variables" and "Bank Holidays".

    The following information is stored in the tab "Variables".

    (A1) Letter (B1) Days to wait
    (A2) 8 (B2) 20
    (A3) 10 (B3) 30
    (A4) 12 (B4) 40
    (A5) 14 (B5) 50


    The following information is stored in the tab "Sent Letters".

    (A1) Letter (B1) Date Sent (C1) New Date
    (A2) 8 (B2) 1-1-2002 (C2) (Formula)
    (A3) .... (B3) .... (C3) (Formula)
    (A4) .... (B4) .... (C4) (Formula)
    (A5) .... (B5) .... (C5) (Formula)

    In the tab "Bank Holidays" there are a number of dates of bank holidays, like
    Easter and Christmas.

    These dates are stored in the cells A1 to A20.

    All entries are made in the tab "Sent Letters".

    When a number is entered in cell A2, (for example "8") and a date
    (for example "January 1st 2002") in cell B2, I want the following actions
    to be done.

    First the "Days to wait" are to be found (20 days) using the VLOOKUP function
    on the tab "Variables".

    The value found there has to be added to the date in cell B2 (on the tab "Sent Letters"),
    and the result (January 21st 2002) has to be stored in cell C2.

    No problem so far....

    But of course there are a couple of weekends (Saturday's and Sunday's) "stored" in
    these 20 days.

    I do not want these weekends to be "counted". I only need "Working" days (Monday through Friday)
    to be counted.

    To make it even more complicated, there could also be some bank holidays (like Easter and Christmas)
    "stored" in these "Days to wait".

    All known holidays are stored in the tab "Holidays" in the same worksheet.

    So what I want to do is the following.
    When a "Letter Number" and a "Date Sent" is entered, the corresponding "Days to wait" has
    to be found using the VLOOKUP function on the tab "Variables".

    If there are weekends or holidays in the "Days to wait", they may not be "counted".

    Could anyone please tell me if this is possible using "formulas" only ?
    (I'm not a VBA programmer.)

    Thanks in advance and best regards,
    Frits Jager
    (The Netherlands)
    In C2 in Sent Letters enter and copy down:

    =WORKDAY(B2,VLOOKUP(B2,Variables!$A$2:$B$100,2,0),'Bank Holidays'!$A$2:$A$20)

    Note. WORKDAY is only available if the Analysis Toolpak add-in is activated (via Tools|Add-Ins).

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,


    Thanks for all your help.
    It works !


    Best regards,
    Frits Jager

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •