| 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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
| 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 | |||
Hybrid Mode
