Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Calculating dates using business days only

  1. #1
    Join Date
    13th July 2004
    Posts
    66

    Calculating dates using business days only

    I need a formula that will calculate a past date. This past date must be 40 buisness days from the current business day.

    Simple Example: =TODAY()-40

    The Simple Example does almost what I need but I need it to count 40 business days (excluding Weekends and Holidays, if holidays can be included). The Simple example counts back 40 business days including weekends and holidays.

    As always,
    Thanks in advance

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,319

    Re: Calculating dates using business days only

    You can use the WORKDAY function...

    =WORKDAY(StartDate,Days,Holidays)

    See the help file...

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    Re: Calculating dates using business days only

    Domenic is right. To finish this one: use

    =WORKDAY(A1;40)

    to know the date 40 workdays from the date in cell A1.

    Wigi

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th July 2004
    Posts
    66

    Re: Calculating dates using business days only

    When I attempt to use the =Workday formula I get the #NAME? error, I've also tried formatting the cell for date. Any Suggestions?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    Re: Calculating dates using business days only

    Make sure that the Analysis Toolpak is checked when you go to Tools > Addins...

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    13th July 2004
    Posts
    66

    Re: Calculating dates using business days only

    Analysis Toolpac was unchecked - Thanks its working now

    Thanks for your help

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th January 2006
    Posts
    524

    Re: Calculating dates using business days only

    Hi,

    add the following macro code to "This Workbook:

    Private Sub Workbook_Open()
    AddIns("Analysis ToolPak").Installed = True
    End Sub

    This macro automatically checks "Analysis ToolPak", which is handy if you send your files to someone else and if they don't "Analysis ToolPak" checked then they will have error messages. I think adding this small macro is very good practice to avoid instances where someone complains that file you sent has error messages.

    Biz

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: May 14th, 2008, 12:53
  2. Calculating Number Of Days Between Dates
    By ericmack in forum EXCEL HELP
    Replies: 3
    Last Post: February 7th, 2007, 00:36
  3. Calculate Business Days Between 2 Dates
    By paulv in forum EXCEL HELP
    Replies: 4
    Last Post: August 25th, 2006, 03:36
  4. Calculating number of days between a choice of dates
    By yellafella in forum EXCEL HELP
    Replies: 6
    Last Post: April 19th, 2006, 03:21
  5. Replies: 3
    Last Post: August 13th, 2003, 06:03

Bookmarks

Posting Permissions

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