1. ## Date span

Hey all,

I'm running Excel 97 SR-2(h).

Here's my quandry... Finding the number of business days between two dates is easy, with the Networkdays function. The hard part is, I need to go the other direction.

When someone comes to me and says, "Wulfgar, what was the date 97 business days ago?.", I have to have an answer. I can't just use a variation of the Days360 function, as I need an exact date, not an approximation. A variation is "Wulfgar, what was the date 15 months ago from today?" Not just any 15 months mind you, but 15 months taking into consideration business days, holidays, and each month have differing amounts of days.

I've played around with all sorts of formulae, and I can get close, but not close enough. Can anyone help?

2. ## Re: Date span

Until you explain how you want to calculate months based on business days, holidays, etc. I really don't know what you want to do. What affect does a holiday have on the number of months. The date of the day 15 months prior to the current date can be calculated by:

<pre>=DATE(YEAR(NOW()),MONTH(NOW())-15,DAY(NOW()))
</pre>

To calculate the date 97 workdays prior to the current date, you can use:

<pre>=WORKDAY(NOW(),-97)
</pre>

The above does not take into account holidays since holidays are different for different companies. There is a third parameter for the Workday function that you can use to specify the holidays. You can do this by putting the holidays in a range of cells and specifying the range as the third parameter.

To use the Workday function you will need to activate the Analysis ToolPak Addin in the Tools/Addins menu.

#### Posting Permissions

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