Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry

    I want to take an existing list in excel showing dates, and add two days to it. If that two days falls on a holiday, I want it to go to the next business day or if the new date falls on a weekend, I want it to go to the next business day. Once the dates are recalculated, i want to populate a calendar to chart the dates so I can look for patterns (maybe shade the dates on the calendar. This way I can get a visual of biweekly or bimonthly, etc. Can this be done?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    If you're using Excel 2003 or before, you can activate the Analysis ToolPak add-in by ticking its check box in Tools | Add-Ins...
    This makes the WORKDAY function available. In Excel 2007 the functions from the Analysis ToolPak are built into the application itself.

    The syntax for WORKDAY is

    =WORKDAY(start_date, number_of_days, holiday_list)

    So for example, with a date in A2, and a list of holidays in H2:H21, you could use

    =WORKDAY(A2,2,$H$2:$H$21)

    to calculate the date 2 business days after the date in A2.

    Note: Excel doesn't always format the result of WORKDAY as a date. If you see a number, simply format the cell as a date.

  3. #3
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much. Now...How do I take those recalculated days and populate and shade them into a calendar to look for trends?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you have some kind of calendar consisting of worksheet cells, you could use conditional formatting to highlight cells.

    I have attached a very primitive example. You will have to recalculate the cells B2:B16 because I created the workbook in a non-English version of Excel.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How can I get the function add-ins or workday...My system at work indicates the file name or path doesn't exist.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='triplescoop' post='781499' date='24-Jun-2009 18:33']How can I get the function add-ins or workday...My system at work indicates the file name or path doesn't exist.[/quote]
    You should be able to tick the checkbox for Analysis ToolPak in the Tools Add-Ins... dialog box.
    If this doesn't work for you can you post a screen shot of what you get.

  7. #7
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    "the feature you are trying to use is on a network resource that is unavailable. Click ok to try again, or enter an alternate path to a folder containing the installation package PRO11.msi in the box below."

    I couldn't get a screen print, so I typed the message I'm getting, above.

    Thanks for any help you can provide.

    triplescoop

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='triplescoop' post='781507' date='24-Jun-2009 19:12']"the feature you are trying to use is on a network resource that is unavailable. Click ok to try again, or enter an alternate path to a folder containing the installation package PRO11.msi in the box below."[/quote]
    What source did you use when you installed Microsoft Office? This sounds like you need to put it back wherever it was when you installed, or put the CD into a drive and browse to the correct drive.

  9. #9
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, we are working on getting the add in's installed. Can somebody show me how to populate a calendar from my list so that the information actually goes into a calendar by month and updated the spreadsheet as time progresses. I need 6 months per page. The last month on the date to be the current month. I need it in order to view patterns to determine if pay is received biweekly, bimonthly, etc. I have attached what I've created using vlookups, but this would require monthly maintenance as time progressed. I'm looking for any input to other ways to make this happen, I'll welcome any ideas. Please help.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Here's an example of a calendar that uses conditional formatting to highlight dates based on data on another sheet.

    This is very old, I haven't looked at it for at least 5 years, but it may give you some idea.

    [attachment=84439:calendar.xls]
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And here is a slightly different take.
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    WOW! I can't believe this....This is exactly what I need, you even hightlighted the payments and everything! Can I get it to show totals by month along the side or somewhere? If I cant get the workday function in 2003 excel because we can't get access to add in's for the analysis tool pak, is there a way to calculate the workdays + 2 using a "created" function to exclude weekends and holidays.

    You are my triplescoop HansV

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The attached version (now a .xlsm file because it contains macros) has:

    - A pivot table summing the payments by month.
    - A list of US holidays for 2009.
    - A series of VBA functions for performing workday maths from Date/Time: Doing WorkDay Math in VBA.
    - Formulas using the dhAddWorkDaysA function instead of WORKDAY so that users don't need the Analysis ToolPak.
    Attached Files Attached Files

  14. #14
    New Lounger
    Join Date
    Jun 2009
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    I copied this to my thumb drive, and received a message...We'll see If i can download onto the computer that I will be working with. Thank you so very much for everything, it is so much appreciated.

  15. #15
    New Lounger
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='781802' date='25-Jun-2009 15:20']Here's an example of a calendar that uses conditional formatting to highlight dates based on data on another sheet.

    This is very old, I haven't looked at it for at least 5 years, but it may give you some idea.

    [attachment=84439:calendar.xls][/quote]


    I was so thrilled to try your calendar! How would I get the calendar to display more than one birthday (event) per day? I'm an experienced/inexperienced excel user who wants to do more, but haven't had the occasion to learn how...

    Thanks!

    Firesheep67

Page 1 of 2 12 LastLast

Posting Permissions

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