Conditional formatting - dates

  • Thread starter Thread starter Barbara Wiseman
  • Start date Start date
B

Barbara Wiseman

I am building a calendar and want to colour the weekend dates and the
holidays. I have managed the weekends by using 'Formula is'
=(F1<>"")*(WEEKDAY(F1,2)>5) and I think I got that from a newsgroup reply, I
am not very good at conditional formatting!
I have tried to do the holidays. I have a list of holiday dates which I use
for the =networkdays calculations, and the range is named 'holiday.dates'.
I have tried 'cell value is' 'equal to' '="holiday.dates", but this does not
work.
I would be grateful if some kind person could put me straight on this.
Barbara
 
Bob,
Thanks for your reply, I could not get your solution to work, and I
discovered that conditional formatting can not refer to data on a different
sheet. I eventually ended up with this
'formula is' =COUNTIF($J$2:$S$8,A11)>0
where $J$2:$S$8 is the area with my dates in.
Your reply did get me started, and I must have been doing something wrong
for it not to have worked, so thank you for your swift reply.
Barbara
 
Barbara,

Conditional formatting can refer to data on another sheet, as long as it is
not directly (that is not Sheet1!A1:H100). You can do it with a named range,
or by using INDIRECT (INDIRECT("Sheet1A1:H100").

I don't understand why yours works with actual references, whereas the one
with named ranges doesn't. The data is on another sheet regardless?
 
Bob,
I 'cheated' and put the holiday dates on the same sheet, not so neat, but it
works!
When the =ISNUMBER(MATCH(F1,holiday.dates,0)) did not work I put it as a
formula and did the match bit on its own, but it kept giving me FALSE. I do
not use ISNUMBER or MATCH much, so was not good at trouble shooting.
Barbara.
 
Barbara,

Do you want to revert it back to another sheet and send me the workbook?
Something is not quite right here, and I hate to leave it like this.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,
Spreadsheet sent, I would be interested to know what silly thing I have
done.
Barbara
 
I am building a calendar and want to colour the weekend dates and the
holidays. I have managed the weekends by using 'Formula is'
=(F1<>"")*(WEEKDAY(F1,2)>5) and I think I got that from a newsgroup reply, I
am not very good at conditional formatting!
I have tried to do the holidays. I have a list of holiday dates which I use
for the =networkdays calculations, and the range is named 'holiday.dates'.
I have tried 'cell value is' 'equal to' '="holiday.dates", but this does not
work.
I would be grateful if some kind person could put me straight on this.
Barbara
Click to expand...

It depends on whether you want the holidays to have the same formatting as the
weekends, and also which you want to have priority if a holiday falls on a
weekend.

One formula you can use to determine if a day is in your Holiday.Dates is:

Formula Is: =COUNTIF(Holiday.Dates,F1)>0

If you want it colored the same as the weekends, then something like:

Formula Is: =AND(F1>0,OR(WEEKDAY(F1,2)>5,COUNTIF(Holiday.Dates,F1)>0))

If you want it colored differently, use two different conditions, and make the
first condition the one you wish to have the priority.


--ron
 
I haven't received it yet!

--
HTH

Bob Phillips

Barbara Wiseman said:
Bob,
Spreadsheet sent, I would be interested to know what silly thing I have
done.
Barbara

as
Click to expand...
 
Bob,
Just sent it again, address seemed the same, I will cross fingers, perhaps
that will do the trick.
Barbara
(If you still do not get it you could email me, if you don't mind
barb at nbpwiseman dot ndo dot co dot uk)
 
Back
Top