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

 

[rlv01]

Not sure I understand. Is this a "Who is buried in Grant's tomb?" question. The date of Christmas will always be December 25, no matter what the year is. Are you looking for the day of the week?

 

[Rick Rothstein]

How about something like this...

[table="width: 500"]
[tr]
	[td]Sub MarkXmasClosed()
  Dim Cell As Range
  For Each Cell In Range("B2", Cells(Rows.Count, "B").End(xlUp))
    If Format(Cell.Value, "mmdd") = "1225" Then Cell.Offset(, 1).Value = "Closed"
  Next
End Sub[/td]
[/tr]
[/table]

 

[paulsolar]

Hi

I have probably explained this incorrectly.

Column B has the dates for many years, column C has the opening hours dependant on the day of the week (day of the week is in column A).

What I want to do is find every 25th December and in column C enter "Closed"

hope that's a bit clearer


cheers

 

[paulsolar]

Hi Rick

I think you are on the right track, I've never managed to figure out how to show a section of a spread sheet, so I've typed it in below. I have put the exact format of the cells

I think i may need to alter the format of you sub's date, any ideas?

Column A Column B Column C

Wednesday 25-Dec-2019 Closes: 09:59 Reopens: 12:59

 

[rlv01]

There are many ways to approach that. VBA is one way, a formula is another.

=IF(AND(MONTH(B7)&DAY(B7)="1225",WEEKDAY(B7,2)>5),"Closed For Christmas","Open For Business")

 

[Rick Rothstein]

Hi Rick

I think you are on the right track, I've never managed to figure out how to show a section of a spread sheet, so I've typed it in below. I have put the exact format of the cells

I think i may need to alter the format of you sub's date, any ideas?

Column A Column B Column C

Wednesday 25-Dec-2019 Closes: 09:59 Reopens: 12:59

If Column B are real dates, the code I posted should have worked directly (the format you choose to display dates in a cell has nothing to do with the underlying true date value). Are you saying my code did not work correctly for you?

 

[MARK858]

Just another way using a mix of formula and autofilter.

Sub GetDec25()
    Application.ScreenUpdating = False
    Columns("D").Insert
    With Range("D1:D" & Range("B" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = _
        "=IF(AND(MONTH(RC[-2])=12,DAY(RC[-2])=25),""Yes"","""")"
        .AutoFilter 1, "<>"
        .Offset(1, -1).Resize(.Rows.Count - 1).SpecialCells(12) = "Closed"
    End With
    Columns("D").Delete
    Application.ScreenUpdating = True
End Sub

Edit: PLEASE SEE RICK'S PREVIOUS POST

 

[paulsolar]

Hi Rick

Your code didn't work for me (although i thought it would looking at it)

 

[MARK858]

Hi Rick

Your code didn't work for me (although i thought it would looking at it)

Rick's code works for me. What does the formula

=ISNUMBER(B3)

give you (where B3 is a cell with one of the dates)?