NETWORKDAYS (for annual leave tracker)

**februarylondon** · 10-08-2009, 04:58 AM

Hi there,

Pretty new to Excel but nonetheless I'm developing a basic Annual leave tracker for an individual member of staff. I've got everything to work by inputting a 'From Date', 'To Date' and by using the NETWORKDAYS function I can show the number of working days has been taken. Also I've set the number of entitled days leave which when subtracted by the total number of days taken so far gives me the remainder of days left that a person can take.

A problem arises when trying to input the formular that don't yet have dates. If the dates cells are left blank the NETWORKDAYS cell displays a '1' which has an effect on the entire tracker. What I believe I need, is some kind of 'IF' function that knows to ignore the NETWORKDAYS operation and input a '0'

Please feel free to open the file and have a look for yourself

Would really appreciate a bit of guidance.

Toby Dyson

**DonkeyOte** · 10-08-2009, 05:13 AM

Given dates are integers you have a few options, eg:

=IF(COUNT(E26:F26)=2,NETWORKDAYS(E26,F26),"")

or

=IF(MIN(E26:F26),NETWORKDAYS(E26,F26),"")

**februarylondon** · 10-08-2009, 05:30 AM

That works a treat - many thanks. Now I'm going to try and find a way to input the national holiday (bank holiday dates) so that these days are excluded in the NETWORKDAYS cell if say there holiday incorporated a bank holiday monday or something.

Thanks DonkeyOte!

**DonkeyOte** · 10-08-2009, 05:33 AM

Now I'm going to try and find a way to input the national holiday (bank holiday dates) so that these days are excluded in the NETWORKDAYS

Yes this is simple enough to do - create a list of the dates and then "name" that range (naming the range is a good way to do this) and then add the additional optional parameter to your function...

=IF(MIN(E26:F26),NETWORKDAYS(E26,F26,holidays),"")

where "holidays" is your new named range.

for info on naming ranges see: http://www.contextures.com/xlNames01.html