Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
13th July 2004
Posts
70

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

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## 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. ## 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. Member
Join Date
13th July 2004
Posts
70

## 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. ## 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. Member
Join Date
13th July 2004
Posts
70

## Re: Calculating dates using business days only

Analysis Toolpac was unchecked - Thanks its working now

Excel Video Tutorials / Excel Dashboards Reports

7. Biz
Established Member
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()
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

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

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