Problem with date and/or cell formatting

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
31
Hello!

I would need advice on cell formatting (I guess).

Having a 4-week-table with dates, I would like to automatically highlight holidays like Easter, Christmas, New Year plus a couple of extra dates like 1th of maj, 6th of june, 6th of december. The cells to format look like c1=$B1+1 (for example).

Any advice on that? I tried by formatting but it did not work at all.

Thankful for all input.
Best regards

Tble looks a bit like this, but for a whole year (4-weeks period, 14 sheets)
Name=sheet1!$B$3 (31.12.2018)=$B1+1 (1.1.2019)=$C$1+1 (2.1.2019)and so on
montuewend

<tbody>
</tbody>
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,382
create a list of holidays on a seperate sheet
then in conditional formatting use a formula

Select the range of dates you want to highlight

=countif( range of list , first cell of the dates you want to highlight ) >0

so if your table of dates are in Sheet2!A1 : sheet2!Z1
and your list of holidays are in Sheet3!A1 : Sheet3!A30

then
for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting


Highlight applicable range >>
Sheet2!A1 : sheet2!Z1


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=countif( Sheet3!$A$1:$A$30, sheet2!A1)>0


FormatÖ [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
31
Hi etaf !

Thanks for your advice.

I followed your detailed instructions but without any result. I thought that maybe the "date formatting" of the cells might be a problem so I made sure that the "holiday-cells" and the usual date cells have the same formatting (in my case D-MMM-ÅÅ), but that did not help either.

Going to try some more, but seems like I am missing something.

I am on Excel 2016 btw.

Best regards
 

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
31
Thanks for the example. Now I am even more confused because that looks the same like what I did. Another date formate, but that would not matter I guess?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,382
date format is UK , as i'm based in UK
change the date cells format to general - and you should see a number - if it stays as a date then its not a date format - although cell+1 would not work

=sheet1!$B$3 (31.12.2018)=$B1+1 (1.1.2019)=$C$1+1 (2.1.2019)

<tbody>
</tbody>
i dont know the layout of the sheet - but $C$1 will not change and so that date will not increment if copied to other cells

try also on cell with the same date in sheet2 and in the sheet three table

sheet2 cell = sheet3 cell

see if you get a TRUE
 

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
31
Okay, the cells are date cells and the same as the holiday cells. Same effect (like none) on the other sheets.
Funny thing is, that I suddenly got the 2nd of jan red - should have been the first.
Don't know but something seems fishy.
Could you have a look at it maybe, in case you got the time?

https://www.dropbox.com/s/kw6zxqvmy1s6uj1/weekly_and_holidays.xlsx?dl=0

Best regards
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,382
the first cell in the formula has to be used
so change A1 to B1 and it correctly highlights the correct date
 

Forum statistics

Threads
1,077,768
Messages
5,336,143
Members
399,067
Latest member
CJWFM

Some videos you may like

This Week's Hot Topics

Top