Format column to calculate network days

[Closed]
Report
-
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
-
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
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.
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
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.
Works great, thank you for your help.
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
your welcome.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!