Diff. between 2 dates, NOT including certain days

Topic

New Reply

(Excel 2000) (In the example below, the dates used are in MM/DD/YY format). In column A, I have certain dates and in column B also, I have dates as well (and these are later in time than those in col A). In col C, I calculate the difference by subtracting the value in col A from that in col B. For example, I may have 3/13/01 in A1, 3/20/01 in B1 and C1 will calculate the difference as 7. But the problem is: I DON’T want to include Saturdays & Sundays, only the weekdays! So in reality, the true difference between the 2 dates in the example above is 5 (i.e. 5 business days). The current formula I am using is (B1-A1); is there a way to modify it so that Saturdays & Sundays are not included in the difference?

Reply | Quote

Replies

What you are looking for is the Networkdays() function. It is part of Excel’s Data Analysis Tool Pak. This is not installed as part of a normal Excel installation. Check Excel help and it will tell you how to install it.

Reply | Quote

Actually, I do have Networkdays installed and its displaying the results without including the weekends. That’s good! To exclude holidays, I understand that I either have to input those dates or refer it to a cell range which has those dates; problem is, I don’t have the dates preprogrammed and it would be tedious to create such a list (“lazy bum” – that’s me). Is there an *easy* way to get the list of holidays (e.g. import it from somewhere on the internet), place it somewhere on the sheet, and give it a name (e.g. “Holidays not on weekends – 2001”)?

Reply | Quote

I don’t know of any ready made lists. I could send you the one I use. It only goes back to 1996.

Reply | Quote

Actually, I would need it for this year and future years. And I forgot to add – the list should be for holidays in the USA that don’t fall on weekends.

Reply | Quote

For a list of holidays, take a look at Outlook.Txt or .HOL.
It’s the file that Outlook uses to populate its calendar.
You’ll have to do some editing, but it’s a start.

Reply | Quote

I located the outlook.txt file and opened it. I notice a few problems right away:
1) The holidays are upto 2002 only;
2) There appear to be many holidays that are not applicable to the USA; some I don’t even recognize! (but I wouldn’t mind having them as holidays !)
3) The list is very long and you are right it would need a lot of editing.
4) There is no mention if the holiday falls on a weekend – I guess one would have to check it manually.
Is there an easier (lazier) way?

Reply | Quote

For anyone who may be following this thread, I found a list of US Federal holidays at
http://www.holidayfestival.com/USA.html.
It appears to be manageable so perhaps with a little work the values can be input into Excel.

Reply | Quote

For Outlook, the countries are identified between [ ]’s.
Other sites are:
and Why even go to work?, also
Chip Pearson has some VBA stuff here.

Reply | Quote

Thanks – those links were helpful.

Reply | Quote

Use the Weekday function to determine if the date falls on a weekend or not (1 and 7 are Sunday and Saturday). That way, import the list of holidays indiscriminately, then let the formula determine which ones are weekends (or weekdays). Filter the list and delete, or have your other cells decide based on what the Weekday result is.

This is my suggestion for a lazy way.

Reply | Quote