Announcement

Collapse
No announcement yet.

Calculating dates using business days only

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Calculating dates using business days only

    You can use the WORKDAY function...

    =WORKDAY(StartDate,Days,Holidays)

    See the help file...

    Hope this helps!

    Comment


    • #3
      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
      Regards,

      Wigi

      Excel MVP 2011-2014

      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

      Comment


      • #4
        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?

        Comment


        • #5
          Re: Calculating dates using business days only

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

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: Calculating dates using business days only

            Analysis Toolpac was unchecked - Thanks its working now

            Thanks for your help

            Comment


            • #7
              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

              Comment

              Working...
              X