| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS calc with 3 columns of dates
I have 3 columns of dates (A1, B1, C1).
Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
=IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B
1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Mendz5 wrote: I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. What do you mean by the result? I cant help you if I don't understand what you want in the cell |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Mark,
Sorry about that. I want the result to be the total number of workdays between the given dates. Thanks, Mendz "Mark" wrote: Mendz5 wrote: I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. What do you mean by the result? I cant help you if I don't understand what you want in the cell |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Mendz,
No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Ok.
First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Thank you very much for the explanation. When you break it up like that, it makes it so much clearer. Thanks again, Mendz "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Hi Mendz,
I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
I don't know, I don't have Excel 2003 so I don't know what a green flag
actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
I wonder what inconsistent formula actually means?
Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, lucky me. Thanks, Mendz |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Thanks, you've been a great help!! Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MIN(J2:K2) ,MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETWORKDAYS( MIN(M2:N2),MAX(M2:N2))+IF(COUNTIF(Q2:R2,"<")=2,NE TWORKDAYS(MIN(Q2:R2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",
IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. When A1, B1 & C1 are populated, only A1 & B1 are added. When B1 & C1 are popluated the result is always "1", no matter what I put in C1. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B 1,C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... I have 3 columns of dates (A1, B1, C1). Several different scenarios: 1. If A1 is blank, but B1 & C1 are populated, I want to put the result in D1. 2. If B1 is blank, but A1 & C1 are populated, I want to put the result in D1. 3. If C1 is blank, but A1 & B1 are populated, I want to put the result in D1. 4. If A1 & B1 are blank, I want to put "N/A" in D1. 5. If A1 & C1 are blank, I want to put "N/A" in D1. 6. If B1 & C1 are blank, I want to put "N/A" in D1. Why are there 3 date fields you ask, the powers that be want it that way, |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
The ABS is just so that I don't have to worry whether the first date is
earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the always 1 problem =IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="" ,B1=""),C1,B1)))) I though A1 and B1 and C1 was not a valid condition, so didn't cater for it. What do you want to happen if they are all present as NETWORKDAYS only works on 2 dates. If you want earliest to latets then perhaps, =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, The formula almost works, unless I fat fingered something, which is entirely possible (I'll double check again). When A1 & B1 are populated the calculation works. When A1 & C1 are populated the calculation works. |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Adds 0 if both dates are not present.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is IF(OR(A1="",B1=""),C1,B1) I pass the two selected dates to NETWORKSDAYS and ABS it in case I don't pass them in date order. That's it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thank you so much, the formula works perfectly. I know the basic functions of excel, could you explain the formula you created. Thanks, Mendz "Bob Phillips" wrote: Mendz, No it was my error on the second part. This corrects the |
|
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Now the boss doesn't want the first day of the range to count. This was the first formula you gave me with the 3 columns: =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) I think a "+1" has to be added but I'm not sure where in the formula to place it. Thanks, Mendz "Bob Phillips" wrote: Adds 0 if both dates are not present. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is |
|
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
You mean that if there are 3 dates, he wants the difference between the
second and the third? BTW, this all seems odd, what is it for? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Now the boss doesn't want the first day of the range to count. This was the first formula you gave me with the 3 columns: =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) I think a "+1" has to be added but I'm not sure where in the formula to place it. Thanks, Mendz "Bob Phillips" wrote: Adds 0 if both dates are not present. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are populated, but I'm having trouble figuring out how to exclude 1 or more cells that are blank. Thanks, Mendz If t "Bob Phillips" wrote: Ok. First, I check if all 3 cells are completed COUNTIF(A1:C1,"<")=3 If this is true, I determine the earliest (MIN(A1:C1)) and latest (MAX(A1:C1)) dates and calculate the difference using NETWORKDAYS(MIN(A1:C1),MAX(A1:C1)) If they are not all completed I check if any two are COUNTIF(A1:C1,"<")<2 and if so error with "N/A" If any two are completed, then either A or B must be one of them, so I get the first date with IF(A1<"",A1,B1) then I determine the second date as either (A or B) or C, depending upon whether the first chosen is A or B (for instance if A is completed, then the second date must be B or C). The formula for this is |
|
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
No, If A1 = 7/06/06, B1 = 7/11/06 and C1 = 7/13/06 He wants to begin counting with A1 plus 1 day If A1 is blank, B1 = 7/11/06 and C1 = 7/13/06 he wants to use B1 + 1 day If A1 = 7/06/06, B1 is blank and C1 = 7/13/06 He wants to begin counting with A1 plus 1 day We have a process where project documents are shuffled from one group to another and he wants to know the duration of time that the documents spend in each group. Some dates we have, some dates are just not available, that's why some of the cells can be blank. Essentially, he has to justify why some projects are being delayed. Thanks, Mendz "Bob Phillips" wrote: You mean that if there are 3 dates, he wants the difference between the second and the third? BTW, this all seems odd, what is it for? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Now the boss doesn't want the first day of the range to count. This was the first formula you gave me with the 3 columns: =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) I think a "+1" has to be added but I'm not sure where in the formula to place it. Thanks, Mendz "Bob Phillips" wrote: Adds 0 if both dates are not present. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are |
|
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
Bob,
Forgot to mention the following: If A1 and B1 are the same date and C1 is blank, then the result should be 1 day duration. The same goes if A1, B1 and C1 are the same date the result should be 1 day duration. If A1 and C1 are the same date and B1 is blank, the result should be 1 day duration. And finally, If B1 and C1 are the same date and A1 is blank the result should be 1 day duration. Sorry for this mess :-( Thank, Mendz "Bob Phillips" wrote: You mean that if there are 3 dates, he wants the difference between the second and the third? BTW, this all seems odd, what is it for? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Now the boss doesn't want the first day of the range to count. This was the first formula you gave me with the 3 columns: =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) I think a "+1" has to be added but I'm not sure where in the formula to place it. Thanks, Mendz "Bob Phillips" wrote: Adds 0 if both dates are not present. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are |
|
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS calc with 3 columns of dates
=IF(COUNTIF(A1:C1,"<")<2,"N/A",IF(MIN(A1:C1)=MAX(A1:C1),1,NETWORKDAYS(MIN(A
1:C1)+1,MAX(A1:C1)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Forgot to mention the following: If A1 and B1 are the same date and C1 is blank, then the result should be 1 day duration. The same goes if A1, B1 and C1 are the same date the result should be 1 day duration. If A1 and C1 are the same date and B1 is blank, the result should be 1 day duration. And finally, If B1 and C1 are the same date and A1 is blank the result should be 1 day duration. Sorry for this mess :-( Thank, Mendz "Bob Phillips" wrote: You mean that if there are 3 dates, he wants the difference between the second and the third? BTW, this all seems odd, what is it for? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Now the boss doesn't want the first day of the range to count. This was the first formula you gave me with the 3 columns: =IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)), IF(COUNTIF(A1:C1,"<")<2,"N/A", ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1))))) I think a "+1" has to be added but I'm not sure where in the formula to place it. Thanks, Mendz "Bob Phillips" wrote: Adds 0 if both dates are not present. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Sorry to be a pest, but what does the zero do? Mendz "Bob Phillips" wrote: The ABS is just so that I don't have to worry whether the first date is earlier or later than the second, saves testing for it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, Thanks again! What did adding the ABS and the zero at the end of each NETWORKDAYS function do? I'm just trying to understand the logic, so that I can use it in the future. Thanks, Mendz "Bob Phillips" wrote: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+ IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+ IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back again. After looking at the results of the most recent formula, I realized that I didn't explain properly what I needed, sorry. Anyway, here it is: There are 3 sets of 2 columns, each set has a beginning and ending date, for example: J2 - 7/26/06 and K2 8/4/06 that is the networkdays that should be counted M2 - 8/8/06 and N2 8/9/06 this duration should be added to the above results Q2 - 8/14/06 and R2 8/16/06 this duration should be added to the above results. Of course, if any one of the sets only has 1 date, then that set should not be included. Here is what I have coded: =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2: N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2)))))) If all cells are populated, then the formula works. If the first set of cells is missing 1 date, I get a "FALSE" in the target cell If the second or third set of cells is missing one date, the formula only returns the duration of the first set of cells. Thanks, Mendz "Bob Phillips" wrote: I wonder what inconsistent formula actually means? Just looked it up and apparently it means that the formula in adjacent cells seem to follow a pattern, and the formula in that cell does not match the formula. So it seems reasonable to just ignore it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, In 2003 when excel thinks there is an error it puts a little green triangle in the upper left corner of the cell. When you click on the flag, a drop down appears with several options. At the top of the drop down it states inconsistent formula. I selected ignore error. The formula does appear to work properly. I took the earliest and the lastest dates and plugged them into one of the formulas that I know work and received the same results. So thanks again! This discussion group has been a great help. Mendz "Bob Phillips" wrote: I don't know, I don't have Excel 2003 so I don't know what a green flag actually means. Do you get the correct answer? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, After entering the formula a green flag appeared in the cell and states that the the formula is inconsistent. Should I ignore it? Mendz "Bob Phillips" wrote: Hi Mendz, I have found a better solution =IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A", ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2)))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mendz5" wrote in message ... Bob, I'm back with something even more complex: 6 columns, but they are not next to each other. J2, K2, M2, N2, Q2, R2 If only 1 cell is populated, then I want to put "N/A" in the target cell If 2 or more cells are populated, then I want to put the number of days in the target cell I think I can create a formula if all cells are |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL should let me establish unlimited numbers of columns. | Excel Discussion (Misc queries) | |||
| Caculating Columns Between Certain Dates | New Users to Excel | |||
| Formula for calc diff between two julian dates | Excel Discussion (Misc queries) | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Convert three separate columns of values to dates | Excel Worksheet Functions | |||
Linear Mode
