# 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>

#### 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 !

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
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!!!

Threads
1,078,447
Messages
5,340,344
Members
399,370
Latest member
salamon

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...