Problem with date and/or cell formatting

[nova_sweet]

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)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]=sheet1!$B$3 (31.12.2018)[/TD]
[TD]=$B1+1 (1.1.2019)[/TD]
[TD]=$C$1+1 (2.1.2019)[/TD]
[TD]and so on[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wend[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

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]

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

 

[etaf]

https://www.dropbox.com/s/xdwl19cdtzjxw1o/Dates_holidays_etaf.xlsx?dl=0

 

[nova_sweet]

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]

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

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]=sheet1!$B$3 (31.12.2018)[/TD]
[TD]=$B1+1 (1.1.2019)[/TD]
[TD]=$C$1+1 (2.1.2019)[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

the first cell in the formula has to be used
so change A1 to B1 and it correctly highlights the correct date

 

[nova_sweet]

You are a genius! Thanks a lot. The rest I will get solved I suppose.

Thank You!!!

 

[etaf]

Also if you select the entire range OR just the columns B:H
it will work for the cells in the other rows with dates

Assuming you are not adding dates or coincidentally numbers in the same range as a date in the other cells

https://www.dropbox.com/s/xdwl19cdtzjxw1o/Dates_holidays_etaf.xlsx?dl=0