Autoformatting of Cells relating to Weekends & Public Holidays

Hello there,

I have been tasked with creating a spreadsheet at work to record our company sickness. My Excel isn't great but better than others, so I pulled the short straw.

The formula is in place (through much trial & error) to populate the calendar days onto the sheet. However I need to show Weekends and Public Holidays separately. So I need a formula that will check the date and either display a blank cell for a weekday, something like ‘W/E’ for a Saturday or Sunday and ‘P/H’ for a Public Holiday.

I have listed the public holidays for the UK this year on another sheet and called the range ‘PubHol’. I’m guessing that Excel will be able to differentiate between weekdays and weekends without having to type them all out.

The weekday cells also need to have a value entered into them if possible to record whether a staff member was off sick or not.

I have tried and tried to get the formula to work, but to no avail. I would be most grateful if anyone out there can help and give me some pointers.

The formula I have at the moment is:
=IF(C2$="n/a","n/a",IF(NOT(ISERROR(MATCH(C$5,'PubHol',0))),"P/H",IF(OR(WEEKDAY(C$5)=7,WEEKDAY(C$5)=1),"W/E","")))

Which is all over the place I know, but I think my brain is starting to melt

Many thanks


Sickness Calendar DRAFT.xls