# Problem with date and/or cell formatting

#### nova_sweet

##### New Member
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 mon tue wend

<tbody>
</tbody>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### etaf

##### Well-known Member
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
Hi etaf !

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
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
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
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
the first cell in the formula has to be used
so change A1 to B1 and it correctly highlights the correct date

#### nova_sweet

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

Thank You!!!