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

davieg

New Member
Joined
Jan 27, 2005
Messages
3
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
David:

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

davieg

New Member
Joined
Jan 27, 2005
Messages
3
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
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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

New Member
Joined
Jan 27, 2005
Messages
3
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
 

Forum statistics

Threads
1,147,851
Messages
5,743,530
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top