conditional format networkdays

Question

Hi All,

I have a calendar in a grid format, Months going down, 1-31 across.

Below this grid we enter the date range, for instance cell G31 '01/01/2013', then cell H31 '05/01/2013'.

What I want the calendar to do, is pick up all the dates in this range & highlight them on the calendar to show an absence for those dates.

I seem to think that you cannot work networkdays into a conditional formula to pick up all the days in the range. I'm hoping that someone can tell me different?

Thanks

Answer 1

I had no idea, I didn't think it was possible at all. Hence I opened it up on here in case someone knew of a way to do it.

Answer 2

... Would it work if I selected a cell range for say H24 to K49 (with H,I & J merged, the same for K,L & M)?

How were you planning to distinguish between up to 26 potentially overlapping periods of absentia?

Answer 3

Thanks All.

I think my main problem will be that there will be more than one entry for the Start/End dates as it is to be used for staff absence.

So I can't really tell how many rows will be entered, in which case the formula Jeeped supplied could prove to be a rather long formula to cover all potential leave.

Would it work if I selected a cell range for say H24 to K49 (with H,I & J merged, the same for K,L & M)?

Answer 4

If you break NETWORKDAYS() into its constituent functionality (e.g. exclude holidays, exclude weekends) you can build a CF formula like,

=AND(ISNUMBER(C2),C2>=$A$16,C2<=$B$16,WEEKDAY(C2,2)<6,NOT(COUNTIF($A$19:$A$28,C2)))

... to achieve something like this,

I've uploaded that example workbook in XLS format to my SkyDrive here for your reference and download. I don't have a copy of Office XP available to me right now to test that but I hope it will point you in the right direction.

CF_Calendar_Workdays.xls

Answer 5

NETWORKDAYS returns the number of days between two values excluding weekends. It does not return the actual dates. In Excel XP, which you are using, NETWORKDAYS is part of the Analysis Tool Pack add-in, you must have this add-in loaded in order to use the function. In Excel 2007, functions that were previously in the ATP have been made native Excel functions.