asked on

Creating Excel date difference of week days only

I'm looking to make an Excel formula that takes the difference of two dates, skipping weekends. I want only weekdays, but the standard subtraction formula counts all calendar days.

How can i do this without installing a third-party add-in? THanks

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael V Bernot

There is also a function that requires the Analysis Toolpak add-in (Tools - Add-Ins check Analysis Toolpak) called NETWORKDAYS.

=NETWORKDAYS(startDate, EndDate)

You can also add a comma after the end date and include the list of Holidays (range of cells) you would also like to exclude. Good Luck.

MVB

zorvek (Kevin Jones)

A word of caution: NETWORKDAYS requires a reference to the Analysis ToolPak.

Kevin

bdietz

ASKER

Thanks.

For formula

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)*1)

Works if cells A1 and B1 contains dates. However, follow up: I want to compare cell A1 to the current date. I tried NOW() in place of B1 and i get a #REF!. I also tried DATEVALUE(NOW()) and i get #VALUE!.

Can you help? Thanks

Michael V Bernot

Bdietz,

Try today().

MVB

zorvek (Kevin Jones)

Note that the formula you chose only counts Mondays. If you want to count week days (Mon-Fri) then use:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={2,3,4,5,6})*1)

The formula requires integer values for the dates (no time component) so you can truncate the value:

TRUNC(B1)

you can truncate the NOW function result:

TRUNC(NOW)

or you can use the TODAY function as MVB stated.

Excel stores dates as double values where the whole part of the number of the day and the fractional part is the time.

Kevin

Michael V Bernot

Daleoran,
Kevin's solution is one that I've added to my KNOWLEDGE BASE. It's a unique way to use the INDIRECT function to turn the date input into a range! It does not require the ANALYSIS TOOL-PAK, but is a little more involved than the built in NETWORKDAYS function that can be accessed with the ADD-IN.
Both solutions do provide a different way to arrive at the same answer to the question. That being said, any recommedation you make would be fine with me!

Michael