Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays and misleading results from blank column
Hello, I am working with two columns of dates and need to work out the number
of working days between two dates, column A and column B with result in Column C. However in some instances column B is blank. Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats ok. Problem is when column B is blank because the result would then come back as -28708. I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could I add a condition to this formula that would translate 'if A1 and B1 are not blank then calculate number of days, if B1 is blank return a 0 (zero) result' Reason I need a zero or blank return is because afterward I'm working out the average number of days. Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays and misleading results from blank column
On Mon, 29 Mar 2010 11:23:01 -0700, Rosemarie
wrote: Hello, I am working with two columns of dates and need to work out the number of working days between two dates, column A and column B with result in Column C. However in some instances column B is blank. Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats ok. Problem is when column B is blank because the result would then come back as -28708. I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could I add a condition to this formula that would translate 'if A1 and B1 are not blank then calculate number of days, if B1 is blank return a 0 (zero) result' Reason I need a zero or blank return is because afterward I'm working out the average number of days. Many thanks Something like (not tested): =if(count(a1:b1)=2, NETWORKDAYS(A1,B1,Holidays),0)) or maybe =max(0,NETWORKDAYS(A1,B1,Holidays))) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays and misleading results from blank column
=IF(B1="","",NETWORKDAYS(A1,B1,Holidays)
Gord Dibben MS Excel MVP On Mon, 29 Mar 2010 11:23:01 -0700, Rosemarie wrote: Hello, I am working with two columns of dates and need to work out the number of working days between two dates, column A and column B with result in Column C. However in some instances column B is blank. Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats ok. Problem is when column B is blank because the result would then come back as -28708. I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could I add a condition to this formula that would translate 'if A1 and B1 are not blank then calculate number of days, if B1 is blank return a 0 (zero) result' Reason I need a zero or blank return is because afterward I'm working out the average number of days. Many thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays and misleading results from blank column
if B1 is blank return a 0 (zero) result'
Reason I need a zero or blank return is because afterward I'm working out the average number of days. If you return 0 when there aren't 2 dates in the cells then that might skew your average. How about returning a blank? =IF(COUNT(A1,B1)<2,"",NETWORKDAYS(A1,B1,Holidays)) -- Biff Microsoft Excel MVP "Rosemarie" wrote in message ... Hello, I am working with two columns of dates and need to work out the number of working days between two dates, column A and column B with result in Column C. However in some instances column B is blank. Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats ok. Problem is when column B is blank because the result would then come back as -28708. I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could I add a condition to this formula that would translate 'if A1 and B1 are not blank then calculate number of days, if B1 is blank return a 0 (zero) result' Reason I need a zero or blank return is because afterward I'm working out the average number of days. Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different results: Networkdays versus date subtraction | Excel Worksheet Functions | |||
SUMIF - Sum_Range is misleading | Excel Worksheet Functions | |||
NETWORKDAYS calculation returns inconsistent results | Excel Worksheet Functions | |||
Grouping the results of Networkdays | Excel Discussion (Misc queries) | |||
networkdays() problem when deleting row or column | Excel Worksheet Functions |