Format column to calculate network days

Closed
drush72 - Nov 12, 2009 at 05:27 PM
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 - Nov 17, 2009 at 03:40 PM
Hello,
I have an excel spreadsheet where I calculate network days between two different dates excluding holidays and weekends. I have the formula right and once I enter the dates I just drag the formula down. My problem is that my dates are scattered through the spreadsheet so after I have dragged the formula down I have to go back and remove the negitive results because dates were not entered.

Is there a way to format the column so that once the dates are entered it will calculate the network days rather than me dragging down the formula?

3 replies

Thanks for your help. Here is the formula

=NETWORKDAYS(I75,K75,{39814;39860;39958;39998;40063;40143;40144;40172})-1
2
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Nov 16, 2009 at 04:10 PM
Try this

=IF(ISBLANK(K75)," ",NETWORKDAYS(I75,K75,DATEVALUE({"24/12/09","25/12/09"})))

put your dates between the brackets enclosed in " and separated by commas. sooo much easier to read the formula rather than having to decipher the date serial numbers for the holidays.

The isblank cell reference can be either I75,or K75, it makes no odds as when the cell referenced is empty it will not action the formula just leave a blank cell.

Hope that was helpful for your purposes.
0
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Nov 15, 2009 at 02:25 PM
could you please supply the formula that you are using, i will have a look and make adjustments as necessary.
what you need to do is write the formula along the lines on only action the cell if there is an entry in the cell otherwise make it blank.

I could explain better if I saw what the formula was that you are using.
1
Works great, thank you for your help.
1
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Nov 17, 2009 at 03:40 PM
your welcome.
0