Number of working days between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out the number of working days between two dates. When I put in a simple

=A1-A2 Where A1 is my start date and A2 is my ending date, I get a total number of days between these two dates. What I want is a simple 5 day work week calculation.

If I put in:

=networking C27-B27
I get a #NAME? result.

Can anyone help?

Thanks
David
 
david said:
I am trying to figure out the number of working days between two dates. When I put in a simple

=A1-A2 Where A1 is my start date and A2 is my ending date, I get a
Click to expand...
total number of days between these two dates. What I want is a simple 5 day
work week calculation.
If I put in:

=networking C27-B27
I get a #NAME? result.

Can anyone help?
Click to expand...

I'm not sure about the format you are trying to use for the desired
function but what you want is NETWORDAYS. Try Help on this or even on "work
days".
 
James Silverton said:
total number of days between these two dates. What I want is a simple 5 day
work week calculation.

I'm not sure about the format you are trying to use for the desired
function but what you want is NETWORDAYS. Try Help on this or even on "work
days".
Click to expand...

I mispelled that function; it is NETWORKDAYS of course.

Jim.
 
I think the correct formula is =NETWORKDAYS(A1,A2)

Don't forget the equal sign or the parentheses.
 
Thanks, that got me most of the way to what I need, but....

How do you add in a list of Holidays? Not just Christmas and Thanksgiving, but additional ones, like the day after Thanksgiving is a company holiday, and I don't want to count that day either.

I know the syntax is:

=NETWORKDAYS(A1,A2,Holidays)

My question is how to define "Holidays?"

----- Carol wrote: -----

I think the correct formula is =NETWORKDAYS(A1,A2)

Don't forget the equal sign or the parentheses.
 
David,

If you have a look at the Excel help (type in "NETWORKDAYS" it provides an
example that inlcudes the usage of holidays.

"Holidays is an optional range of one or more dates to exclude from the
working calendar, such as state and federal holidays and floating holidays.
The list can be either a range of cells that contains the dates or an array
constant of the serial numbers that represent the dates."

So for example, you could have =networkdays(A2,A3,A4:A6) where A4:A6
contains dates such as 1/6/03, 7/9/02, 1/8/01.

Cheers,
Katherine
David said:
Thanks, that got me most of the way to what I need, but....

How do you add in a list of Holidays? Not just Christmas and Thanksgiving,
Click to expand...
but additional ones, like the day after Thanksgiving is a company holiday,
and I don't want to count that day either.
 
Hi,

Is there any way I can get the number of working days in a 6-da
workweek? Some crazy folks work on a Saturday and I need to count it a
a workday. NETWORKDAYS takes Sat+Sun as a weekend.

Bads

14/Jun/2004

:confused
 
Bads,

With the start date in A1, and the finish date in A2:

=A2-A1+1-INT((A2-A1)/7)-IF(WEEKDAY(A2)<WEEKDAY(A1),1,0)

This doesn't account for holidays - if they work Saturdays, they can darn
well work holidays, too... <vbg>


HTH,
Bernie
MS Excel MVP
 
Hi Bads!

But if you are not in the Bernie school of slave drivers:

=1+B1-A1-INT((B1-WEEKDAY(B1)-A1+8)/7)-SUMPRODUCT((HolidayRange>=A1)*(HolidayRange<=B1)*(WEEKDAY(HolidayRange)<>1))

HolidayRange is a named range of cells that contains public holidays.

As with Bernie's solution, the calculation is inclusive of the start
and finish day. If, like NETWORKDAYS, you want a B1-A1 type count,
just remove the 1+ at the beginning.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Bernie Deitrick said:
Bads,

With the start date in A1, and the finish date in A2:

=A2-A1+1-INT((A2-A1)/7)-IF(WEEKDAY(A2)<WEEKDAY(A1),1,0)

This doesn't account for holidays - if they work Saturdays, they can
darn
well work holidays, too... <vbg>


HTH,
Bernie
MS Excel MVP
Click to expand...
 
But if you are not in the Bernie school of slave drivers:

Sure! Treat 'em soft and the next thing you know, they'll want coffee
breaks! Why when I was a lad, we used to _dream_ of taking holidays off....
 
Back
Top