Finding Christmas Day

[paulsolar]

Hi All

I've been on this all afternoon and have finally given up.

In column B I have dates and in column C I have the opening hours off the office.

I can figure out how to find the date of Christmas (25-Dec ) if I have the year to go with it, for example 25-Dec-2019. What I cant figure out is how to find the 25-December for every year in the date column.

What I'm attempting to do is loop through all the dates in column B and if it's Christmas day in corresponding cell in column C enter "Closed" There may already be information in column C that needs to be overwritten.

Without being able to do this I have to I cant automate an upload automatically to a website calendar.

Any help or ideas of a method of doing this would be greatly appreciated.

cheers

Paul

 

[paulsolar]

Hi RLV

the formula works

i think mixing this with Marks code will work, i just need to figure out how to do it

 

[paulsolar]

Isnumber is returning "True"

 

[MARK858]

Hi RLV

the formula works

i think mixing this with Marks code will work, i just need to figure out how to do it

Are you saying my code with the formula already in it doesn't work?

 

[paulsolar]

yes, let me try your formula on it's own a moment

 

[paulsolar]

Hi Mark

if i put the formula =IF(AND(MONTH(B2-2)=12,DAY(B2-2)=25),"Yes","") in and copy it down it returns Yes in column D next the correct dates

 

[MARK858]

So how is the macro not working? what results are you getting? btw how is the formula by rlv01 working as it only finds Xmas day if it falls on a weekend as it should have an OR rather than an AND?

 

[rlv01]

if i put the formula =IF(AND(MONTH(B2-2)=12,DAY(B2-2)=25),"Yes","") in and copy it down it returns Yes in column D next the correct dates

Odd. If Col B contains dates, why would you subtract 2? Are you sure you are not confusing the RC[-2] row/col cell reference in Mark's code with some kind of math operation?

 

[paulsolar]

I only copied rlv's formula down two years both of which had 25th on a weekday so i never noticed if it didnt work on a weekend.

with your sub, it runs fine no errors, but nothing changes for the 25th dec

 

[MARK858]

I only copied rlv's formula down two years both of which had 25th on a weekday so i never noticed if it didnt work on a weekend.

No it only works if Xmas day falls on a Weekend.

Please upload your workbook to www.box.com or www.dropbox.com, mark it for sharing and post the link it provides in the thread.

 

[paulsolar]

Odd. If Col B contains dates, why would you subtract 2? Are you sure you are not confusing the RC[-2] row/col cell reference in Mark's code with some kind of math operation?

you are correct i was confusing row column. it's been a long day