NETWORKDAYS Help!

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

Guest

Hello again,

Thanks for the help so far. Here's the deal, I've made a spreadsheet (with a little help from my friends here) that will calculate the amount of time that has passed from when I submit for a quote and the quote is returned. NETWORKDAYS includes the date entered as the Starting Date as a day. In other words, for my purposes, Dec 1 - Dec 3 is 2 days. This function counts it as 3. How can I get it to count it as 2? I tried just subtracting 1 from the end date but if I have a quote submitted on 12/1 and returned on 12/1 I get a calculated return time (in days) of -1??? I need it to be 0. Any suggestions?

Also have a NEWBIE question: Why is there no function for subtraction?

Thanks for all the help!!!

Matt
 
=IF(A1=B1,0,NETWORKDAYS(A1,B1,Holidays))

where A1 is the start date and B1 the end date

--

Regards,

Peo Sjoblom

Matt said:
Hello again,

Thanks for the help so far. Here's the deal, I've made a spreadsheet
Click to expand...
(with a little help from my friends here) that will calculate the amount of
time that has passed from when I submit for a quote and the quote is
returned. NETWORKDAYS includes the date entered as the Starting Date as a
day. In other words, for my purposes, Dec 1 - Dec 3 is 2 days. This
function counts it as 3. How can I get it to count it as 2? I tried just
subtracting 1 from the end date but if I have a quote submitted on 12/1 and
returned on 12/1 I get a calculated return time (in days) of -1??? I need
it to be 0. Any suggestions?
 
OK, good idea, but I'm going to complicate it on you. I'm not sure if this is exactly it, but I have something like this
=IF(B1=0,"",NETWORKDAYS(A1,B1,Holidays)
It's a check to make sure the calculation isn't made until the end date is in place. Soooooo, how do I incorporate the check that you provided AND get the function to return a value that is not calculated with the start date included?

Mat
 
One way

=IF(B1=0,"",IF(A1=B1,0,NETWORKDAYS(A1,B1,Holidays)-1))

note that I subtracted one day if the end date was later then start date as
you wanted

--

Regards,

Peo Sjoblom

Matt said:
OK, good idea, but I'm going to complicate it on you. I'm not sure if
Click to expand...
this is exactly it, but I have something like this:
=IF(B1=0,"",NETWORKDAYS(A1,B1,Holidays))
It's a check to make sure the calculation isn't made until the end date is
Click to expand...
in place. Soooooo, how do I incorporate the check that you provided AND get
the function to return a value that is not calculated with the start date
included??
 
Hello again,

Thanks for the help so far. Here's the deal, I've made a spreadsheet (with a little help from my friends here) that will calculate the amount of time that has passed from when I submit for a quote and the quote is returned. NETWORKDAYS includes the date entered as the Starting Date as a day. In other words, for my purposes, Dec 1 - Dec 3 is 2 days. This function counts it as 3. How can I get it to count it as 2? I tried just subtracting 1 from the end date but if I have a quote submitted on 12/1 and returned on 12/1 I get a calculated return time (in days) of -1??? I need it to be 0. Any suggestions?

Also have a NEWBIE question: Why is there no function for subtraction?

Thanks for all the help!!!

Matt
Click to expand...

A1: 12/1/2003
A2: 12/1/2003

=NETWORKDAYS(A1,A2) - 1 --> 0 on my machine.


--ron
 
Maybe I should have checked that first <g>

--

Regards,

Peo Sjoblom

(with a little help from my friends here) that will calculate the amount of
time that has passed from when I submit for a quote and the quote is
returned. NETWORKDAYS includes the date entered as the Starting Date as a
day. In other words, for my purposes, Dec 1 - Dec 3 is 2 days. This
function counts it as 3. How can I get it to count it as 2? I tried just
subtracting 1 from the end date but if I have a quote submitted on 12/1 and
returned on 12/1 I get a calculated return time (in days) of -1??? I need
it to be 0. Any suggestions?
 
OK guys the formula worked great. Got a weird twist of events for you. I have a conditional format in place such that if the result of the formula you gave me is greater than 3 than the font color should change to yellow. All of the cells that show the quote submitted for and returned on the same day show in yellow. Why? How can this be fixed?
This is the formula I used: =IF(B1=0,"",IF(A1=B1,0,NETWORKDAYS(A1,B1,Holidays)-1)

Matt
 
There is one other problem that I forgot. I have a different cell set to calculate the average number of days that it takes to have the quotes returned. It is not calculating properly. What formula do you suggest?

matt
 
I can't reproduce that, are you using [cell value is greater than] in the
conditional formatting? You can't use
anything but built in functions directly like if you were to use [formula
is] and NETWORKDAYS which is part
of the Analysis ToolPak.

--

Regards,

Peo Sjoblom

Matt said:
OK guys the formula worked great. Got a weird twist of events for you. I
Click to expand...
have a conditional format in place such that if the result of the formula
you gave me is greater than 3 than the font color should change to yellow.
All of the cells that show the quote submitted for and returned on the same
day show in yellow. Why? How can this be fixed?
 
Yes, I am using cell value is greater than . . . and in the last box I enter the value 3. Then anything with a Start Date and End Date that is the same properly shows 1 day as the turn around time, but is highlighted yellow because of the conditional format. All of the other cells are OK. I adjusted the formula so that anything that started on and finished on the same day showed as 1 day rather than 0 days because with a 0 value in there the Average Turn Around column was unfairly not counting the days that I got the quotes back very quickly. Maybe this is the problem because not only is my conditional format messed up but the cell that holds the Average is messed up. Does the fact that I'm using Excel 97 have anything to do with this? I can upload the sheet for you to see or write in the spreadsheet values if it will help. I know it will help me . . . I am just so lost

Thanks for continuing to try

Matt
 
Back
Top