Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
Hi,
Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. -- TIA AFJr |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
Maybe:-
=IF(AND(B8=0,B8D5),(NETWORKDAYS(B8,$D$5)),NETWORK DAYS($D$5,B8)) Mike "AFJr" wrote: Hi, Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. -- TIA AFJr |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
Hi Mike,
Thanks for trying but that did not give me the desired result. I've tried multiplying the result using -1 in order to get a negative number if the release date is later than the due date but that did not work either. -- TIA AFJr "Mike H" wrote: Maybe:- =IF(AND(B8=0,B8D5),(NETWORKDAYS(B8,$D$5)),NETWORK DAYS($D$5,B8)) Mike "AFJr" wrote: Hi, Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. -- TIA AFJr |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
The do it the other way around
=IF(AND(B8=0,B8D5),(NETWORKDAYS($D$5,B8)),NETWORK DAYS(B8,$D$5)) Mike "AFJr" wrote: Hi Mike, Thanks for trying but that did not give me the desired result. I've tried multiplying the result using -1 in order to get a negative number if the release date is later than the due date but that did not work either. -- TIA AFJr "Mike H" wrote: Maybe:- =IF(AND(B8=0,B8D5),(NETWORKDAYS(B8,$D$5)),NETWORK DAYS($D$5,B8)) Mike "AFJr" wrote: Hi, Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. -- TIA AFJr |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
On Wed, 28 Nov 2007 05:32:01 -0800, AFJr
wrote: Hi, Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. It's helpful if you give the results you are getting, along with sample inputs, actual outputs and desired outputs. But as a rough guess C8: =IF(B8=0,"",NETWORKDAYS(B8,$D$5)) B8: 10-Jan-07 D5: 15-Jan-07 C8: -- 4 B8: 30-Jan-07 D5: 15-Jan-07 C8: -- -12 --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
I went back to a previous document that was already filed by the quality
department. Without changing anything on the form and just playing with the dates I got the desired result - negative - when the release date was later than the due date. I was STUNNED! I modidfied the formula (below) to give me a blank field in C8 if A8 is blank and to make C8=0 if the two dates were the same. I have no idea why it works in one doc but not the other. I compared the two side by side scratching my head because they were exactly the same. eventual solution: =IF(A8=0,"",IF(A8=$C$5,0,NETWORKDAYS(A8,$C$5))) C5 is Due Date A8 is Released Date C8 is result in days (+/-) Senario: released date prior to due date: C5 = 1-jan-07 A8 = 12-dec-06 C8 = 1 Senario: released date later than due date: C5 = 1-jan-07 A8 = 2-jan=07 C8 = -1 Senario: released date equals due date: C5 = 1-jan-07 A8 = 1-jan-07 C8 = 0 Thanks for everyones help who responded. Any idea why the docs with the identical formulas wouldn't work the same? AFJr "Ron Rosenfeld" wrote: On Wed, 28 Nov 2007 05:32:01 -0800, AFJr wrote: Hi, Objective: A work order release is received and I enter that date into cell B8. In cell D5 resides the due date an item is due. In cell C8 I want to calculate the days to the due date (this works). If the date entered is later than the due date, I need to show the days late (this appears to work except I need to show it as a negative number) and that is my problem. This is the formula I'm using, it resides in cell C8: =IF(B8=0,"",(NETWORKDAYS($D$5,B8))) Am I using the right function? If not, can someone point me in the right direction because I am pretty much lost. It's helpful if you give the results you are getting, along with sample inputs, actual outputs and desired outputs. But as a rough guess C8: =IF(B8=0,"",NETWORKDAYS(B8,$D$5)) B8: 10-Jan-07 D5: 15-Jan-07 C8: -- 4 B8: 30-Jan-07 D5: 15-Jan-07 C8: -- -12 --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
On Wed, 28 Nov 2007 08:01:01 -0800, AFJr
wrote: eventual solution: =IF(A8=0,"",IF(A8=$C$5,0,NETWORKDAYS(A8,$C$5))) C5 is Due Date A8 is Released Date C8 is result in days (+/-) Senario: released date prior to due date: C5 = 1-jan-07 A8 = 12-dec-06 C8 = 1 Senario: released date later than due date: C5 = 1-jan-07 A8 = 2-jan=07 C8 = -1 Senario: released date equals due date: C5 = 1-jan-07 A8 = 1-jan-07 C8 = 0 Thanks for everyones help who responded. Any idea why the docs with the identical formulas wouldn't work the same? That's often a problem with data, but if you post the errors you are seeing ... Your first example, above, doesn't make sense as there are about 15 workdays between 12/12/06 and 1-jan-07 (without the holidays argument). Also, NETWORKDAYS always includes the first and last day, so if that's not what you want, then you will need to subtract or add one from all of your results. Awaiting clarification ... --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS: using correct funtion?
On Wed, 28 Nov 2007 15:48:10 -0500, Ron Rosenfeld
wrote: Also, NETWORKDAYS always includes the first and last day, so if that's not what you want, then you will need to subtract or add one from all of your results. Here is a formula that adjusts the result to give a difference exclusive of the starting date, as would result from "normal" subtraction/addition: =NETWORKDAYS(ReleasedDate,DueDate)+IF(ReleasedDate <=DueDate,-1,1) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How create my own Funtion | Excel Worksheet Functions | |||
VLOOKUP, correct funtion? | Excel Worksheet Functions | |||
If Funtion | Excel Worksheet Functions | |||
If funtion help | New Users to Excel | |||
IF funtion | Excel Discussion (Misc queries) |