I am tring to colour fill cells using conditional formatting in a column containing date and time data (format: Mon, 2 Jun 09, 8:00). I require four conditional formulas for this one column:
1. Business Hour (Mon – Fri, 8:00am to 5:00pm)
2. Weekday after business hours (Mon – Fri, 5:00pm to 8:00am)
3. Friday after hours through to end of weekend (Fri 5:00pm to Monday 8:00am).
4. Date in the past
I have looked all over and have come close at least with the WEEKDAY function (and a handy video explaining how to highlight Sat&Sun using it: http://www.articlesbase.com/videos/5min/79935061 ).
So I really need help to get the day and time filtering into one formula. Are there any gurus out there in the know?
p.s. Initially I was going to have Date in one column and Time in another – if it’s easier, Id actually prefer this way – but am happy with anything!
Thanks in advance!