Networkdays problem
 

Hi
I'm trying to use the network days function to calculate how many days
there are between today() and a list of project deadlines.

The function i'm using is *NETWORKDAYS($N$2,E3)*
Where $N$2 is a cell containing the function *Today()* returning todays
date. and the E column contains the project dead lines

Cell N2 and the E column are formatted as CUSTOM d/m/yy (UK)

As an example of the problem if todays date N2 is 29/03/05
And the E column contains 30/5/2005 the figure returned is 14
If the E column contains 14/4/2005 the figure returned is 13
If the E column contains 14/6/2005 the figure returned is 25
If the E column contains 21/6/2005 the figure returned is 1

Its all over the place.

I've tried changing the Format of the cells to the US format i.e.
mm/dd/yy but the format makes no odds.

Ive tried removing the function *today()* from N2 and replacing it with
a date, same problem.

I dont think its the way i have put the formula in, i think it has
somthing to do with the settup of the spreadsheet.

Can anyone sugest what the problem is?

Regards
James


--
JamesDMartin
------------------------------------------------------------------------
JamesDMartin's Profile: http://www.msusenet.com/member.php?userid=272
View this thread: http://www.msusenet.com/t-3344333

On Tue, 29 Mar 2005 06:55:02 -0500, JamesDMartin
wrote:


Hi
I'm trying to use the network days function to calculate how many days
there are between today() and a list of project deadlines.

The function i'm using is *NETWORKDAYS($N$2,E3)*
Where $N$2 is a cell containing the function *Today()* returning todays
date. and the E column contains the project dead lines

Cell N2 and the E column are formatted as CUSTOM d/m/yy (UK)

As an example of the problem if todays date N2 is 29/03/05
And the E column contains 30/5/2005 the figure returned is 14
If the E column contains 14/4/2005 the figure returned is 13
If the E column contains 14/6/2005 the figure returned is 25
If the E column contains 21/6/2005 the figure returned is 1

Its all over the place.

I've tried changing the Format of the cells to the US format i.e.
mm/dd/yy but the format makes no odds.

Ive tried removing the function *today()* from N2 and replacing it with
a date, same problem.

I dont think its the way i have put the formula in, i think it has
somthing to do with the settup of the spreadsheet.

Can anyone sugest what the problem is?

Regards
James

Date input parsing is determined by your regional settings, not by the Excel
format. The Excel format only determines how the input is displayed.

As a test, try entering the dates unambiguously. Such as:

=DATE(2005,5,30)
=DATE(2005,4,14)
=DATE(2005,6,14)
=DATE(2005,6,21)

=TODAY() in N2 is unambiguous

If that gives correct answers, then check under your Windows regional settings
(or the equivalent if you're using a different OS) that your date settings are
the way you are entering your data.


--ron

Cheers Ron
I tried what you said, entering the date =DATE(2005,6,21) format and
the results were the same.

I Found i had the results column formatted to dd instead of number or
general.
When you insert a column it copys the format from the the exising
coloumn to its left.

Thanks for the help


--
JamesDMartin
------------------------------------------------------------------------
JamesDMartin's Profile: http://www.msusenet.com/member.php?userid=272
View this thread: http://www.msusenet.com/t-3344333

On Wed, 30 Mar 2005 04:05:40 -0500, JamesDMartin
wrote:

I Found i had the results column formatted to dd instead of number or
general.

Yep, that'll do it. Glad you got it figured out.


--ron