if true, do this or that, else....

[davieg]

Hi,

I have been having some problems with the NETWORKDAYS formula, regarding same dates and weekends. That is not my question however.

To get round my NETWORKDAYS formula issues, I am using this formula:

=IF(ISNUMBER($H3),IF(OR(COUNTIF(Weekends,H3),NETWORKDAYS($G3,$H3),(NETWORKDAYS(G3,H3)-1)),""))

This appears to work, except my result cell is blank, even when H3 is populated. My previous formula did this, although the NETWORKDAYS result was inconsistent:

=IF(ISNUMBER($K4),(NETWORKDAYS($J4,$K4,Weekends)-1),"")

I am sure I am missing something simple, but I am stumped. If anyone has any ideas on my networkdays issue also, you are very welcome.

Thanks
David

 

[Oaktree]

David:

To back up a step, what values are in H3 and G3, and what are you trying to calculate?

 

[davieg]

Hi Oaktree,

H3 and H4 are date fields (Planned and Actual), where I am using the custom format dd/mm/yyyy - I'm in the UK. My problem generally occurs when H3 and H4 are the same, I am tracking project slippage. so this can and hopefully occurs. I use (networkdays(H3,H4)-1) so that 28/01/2005 : 28/01/2005 produces the result 0, rather than 1 as Excel calculates. This works fine, but some dates fall on a weekend. To my knowledge, Networkdays should calculate weekends, however the -1 argument will produce -1, where the result should be 0. These dates are the same when checking their underlying serial numbers BTW.

To counteract this, I have devised a range for Weekend dates (yes Excel should do this anyway), and then apply either networkdays(H3,H4) or (networkdays(H3,H4)-1) depending on whether or not the date exits in the weekend range.

My logic is if H3 is a date, do this if a weekend, do that if a weekday and if H3 is blank, do nothing. Currently, my result is blank despite H3 and H4 being populated.

Long explanation sorry, but hopefully explainsmy prob.

Cheers
David

 

[fairwinds]

Hi,

Try:

=NETWORKDAYS(WORKDAY(H3,1),WORKDAY(H4,1))-1

 

[P Sitaram]

Take the True part of the first IF (which is a second IF):
IF(
condition: OR(COUNTIF(Weekends,H3),NETWORKDAYS($G3,$H3),(NETWORKDAYS(G3,H3)-1)),

True result:"") (i.e., blank)
False result: Not specified hence False

Hence, check the parentheses first.

 

[davieg]

Fairwinds,

Superb. That works super nice thank you. I had thought of using Workday, but did not think to nest it within the Networkdays formula. This also supports my original value-if-false argument of "".

Thanks Psitaram, I'l take a closer look at my parathenthesis in future.

Cheers
David