counting dates

[jenniss]

in cells D & E i have a series of dates. I want to put a formular E - D =
calculated number of days and i also dont want it calculating weekend?

the purpose of this is excel to work out how many days it takes to serve a
document

is this possible and if so what is the formular please

 

[Roger Govier]

=NETWORKDAYS(Start_date,End_date,holidays)

Holidays would be a range containing the dates of public holidays you wish
to de excluded from your calculation.
for example in your case, if you entered the range of holiday dates in cells
H1:H10
=NETWORKDAYS(D1,E1,H1:H10)

 

[jenniss]

Thank you for your reply

I have now listed the dates to avoid in cells H1, H2 etc etc


i have now put your forular in the first cell (F2) put it is coming up with
the value #name?

the formular i put was =NETWORKDAYS(D1,E1,H1:H10)

also is there a way to apply the formular to all of the cells in row F as I
am currently putting the formular in cell one by one

Thank you in advance

 

[Roger Govier]

Hi

I should have said that you need to have the Analysis Toolpack loaded.
Go to Tools>Addins and select the tick box for Analysis Toolpak and click
OK.

Having put the formula in F2 amend it to lock the range of holiday dates as
follows by inserting $ signs as shown

=NETWORKDAYS(D2,E2,$H$1:$H$10)

then, hover with your cursor over the bottom right corner of the cell
containing the formula until it turns to a black cross.
Hold down the left mouse button and drag down and the formula will copy down
changing the references for the cells with dates, but holding the range of
cells with holiday dates as constant.

 

[jenniss]

Thank you

I have now done as mentioned and the cell is now saying #value!

sorry to be a pain a thank you again for assissting me

 

[Roger Govier]

Are you sure that they are valid dates in the cells and not text values that
look like dates?
Try entering in other cells on the sheet D2+1 and E2+1.
Do these give you a #value error as well?, if so then you are not dealing
with real dates.

 

[jenniss]

I did highlight cell D clicked - format - cells - Date (chose 14 mar 2001)

is this incorrect cell format? what should i choose for the cell with the
formula in it?

 

[Roger Govier]

Hi Jenniss
Firtsly, I am having difficulty in keeping this going as a proper thread. My
newsreader won't let me do a further reply to a response froma reeply I have
already made, hence I am having to return to the original posting each time.

The formula works fine for me.
If you want to send me a copy of your file directly to me email address, I
will take a look at it for you and see if I can figure out what is wrong.

just remove the words NOSPAM from my address to send direct.

 

[jenniss]

very sorry but i cant find your email address, and dont know how i can find
it.

 

[jenniss]

sorry found it

Hi Jenniss
Firtsly, I am having difficulty in keeping this going as a proper thread. My
newsreader won't let me do a further reply to a response froma reeply I have
already made, hence I am having to return to the original posting each time.

The formula works fine for me.
If you want to send me a copy of your file directly to me email address, I
will take a look at it for you and see if I can figure out what is wrong.

just remove the words NOSPAM from my address to send direct.