Working Days

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I have this formula in A1
=(B1-C1)

in B1 I have one date
in C1 I have a date which is a few days later than in B1

The formula in A1 Works out the days between the dates in B1 AND C1.

I want a formula that will do this but alos take into account weekends
If the dates roll over a weekend. I want it to minus those days.

Any ideas
 
Hi Ian!

Use:
=NETWORKDAYS(B1,C1)

It's an Analysis ToolPak function. It also accepts a third argument of
a range that contains holidays.

Usually B1 will be earlier than C1 but it's not essential and in B1>C1
it will return a negative.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Ah ok I figured out i need to enable the add in.

Show how do I put in holiday exceptions from a Cell Range
 
See responses to other question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Ian

Type in a list of the dates for holidays say in cells A1:A10
Then use
=NETWORKDAYS(B1,C1,A1:A10)

Alternatively, name the range of cells containing your holiday datesas
holidays, and
=NETWORKDAYS(B1,C1,holidyas)
 
Enter the holiday dates in a range of cells, say F1:F10, one date
per cell. Then, include that range of cells in the NETWORKDAYS
function. E.g.,

=NETWORKDAYS(B1,C1,F1:F10)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Ian!

Note with the holidays argument that if the range containing the
holidays is not on the same sheet, it must be named.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman Harker said:
Note with the holidays argument that if the range containing the
holidays is not on the same sheet, it must be named.
Click to expand...

Are you sure about that? It seems to work without it for me.

Bob
 
Hi Bob!

Absolutely right! I was wrong. Now I'm mystified as to where I got
that from. It doesn't appear to relate to any function argument in my
list. There are probs if you enter as an array of strings but it will
even take a range off a closed workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top