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

I *think* I see what you're trying to do.
With
C5 containing a date

This regular formula returns:
P/H.............if that date is on the PubHol list
W/E............if that date is on a weekend
N/A............if the cell contains N/A
Otherwise...an empty string ""
EDITED TO PRESENT THIS SHORTER VERSION

Please Login or Register  to view this content.

Is that something you can work with?

Thanks for coming back so quickly Ron, WEEKENDS ARE WORKING!!!

To clarify, it is only the cells in Row 2 on each sheet that will contain the date information.

C5 should return 'W/E' as the 1st Jan was a Sunday, C6 should return 'P/H' as it relates to 2nd Jan and that date is specified in the Public Holiday sheet. Then cells C7-C10 should be blank as they are weekdays.

So I have amended the formula you gave me replacing C5 with C2, adding in the absolute referencing to make it easier to populate my table.

Please Login or Register  to view this content.

So now with that formula all of the weekends in a month display correctly . However weekdays and public holidays return '#NAME?'

Do I need to write the dates of the Public Holidays in a different format on the sheet?

Sickness Calendar DRAFT1.xls

I see the problem in your posted workbook. You named the range of public holidays as "PubHols"...not "PubHol".
So the formula in C5 should be:
=IF(C$2="N/A","N/A",IF(WEEKDAY(C$2,2)>5,"W/E",IF(COUNTIF(PubHols,C$2),"P/H","")))
instead of
=IF(C$2="N/A","N/A",IF(WEEKDAY(C$2,2)>5,"W/E",IF(COUNTIF(PubHol,C$2),"P/H","")))

A simple way to correct the existing formula in your worksheet is to:
• Select the "Jan 2012" sheet
• CTRL+H....shortcut for Find/Replace
...Find what: pubhol
...Replace with: pubhols
Click: Replace all

Does that help?