Formula to Count weekdays only - excluding Holidays, too

**joeljoel** · 11-18-2010, 01:08 PM

I need some help tweaking a formula (version 2007) that counts working days each month. I have it working so far except I can get it to omit holidays. I have a list of holidays on separate tab that can be referenced.

Anyway, easiest thing is to attach the spreadsheet. I need help with the column G formula. It doesn't double count the Holidays, but I would strongly prefer the have the cell blank (or even out an "H" in the cell) if that days is one of the Holidays.

Thanks in advance for any help.

**martindwilson** · 11-18-2010, 01:20 PM

add another if
=IF(COUNTIF(HOLIDAYS!$C$4:$C$14,A5),"h",IF(OR(F5="Sat",F5="Sun")," ",SUMPRODUCT(--(WEEKDAY(A5+1-ROW(INDIRECT("1:"&DAY(A5))),2)<6),--ISNA(MATCH(A5+1-ROW(INDIRECT("1:"&DAY(A5))),HOLIDAYS!$C$4:HOLIDAYS!$C$14,0))))

**joeljoel** · 11-18-2010, 02:25 PM

Thanks, Martin! That's perfect. I figured it would be relatively simple for someone who know what to do. I tried something along these lines before, but couldn't get it to work - I was missing the COUNTIF function.

I think I'll enjoy a mojito in your honor!

**joeljoel** · 11-19-2010, 12:32 AM

Pardon my ignorance on these things. But I have a follow up question for this same spreadsheet.

I need a formula in another column that uses the last work day or next to last work day in each month and returns a value (say 1) if this is true.

Thank you!!!