Check if date is Sunday

[kakiebos]

Hi Everyone.

I have a UserForm where the user must update the Public Holidays (South African) for the year. Basically the user must only change the year and then the date of Easter Friday. All the other dates will auto update in the relevant Table ("tblPPH") In this UserForm, there will be a complete list of all the Public Holidays' names as Labels as well as the date. Also a label, but only the day and month. There is a TextBox for the year at the top of the userform. There is also a TextBox for the date of Easter Friday.

At the end of the year, when the new years' holiday dates need to be updated, the user only changes the year in the relevant TextBox. I have some code that runs after the year is changed.

I would like to change the Label's front color to change to red if the Public Holiday falls on a Sunday.

Private Sub tbYear_AfterUpdate()
    
  Dim tblPPH As ListObject
  
Set tblPPH = ShInstructions.ListObjects("PublicHolidays")
  
   
  'Test for Sunday
  If Me.pphWomens & Me.tbYear = "09 August" & Me.tbYear Then ' I need this line to check for Sunday ("09 August" & Me.tbYear)
    Me.lWomens.ForeColor = &HFF&
  Else
    Me.lWomens.ForeColor = &H80000008
  End If


End Sub

Please help.

There are 10 holidays that must be tested if it falls on a Sunday. All the labels are:
Me.lNewYears
Me.lHumanRights
Me.lGoodFriday
Me.lEasterSunday
Me.lFamDay
Me.lFreedom
Me.lWorkers
Me.lYouth
Me.lWomens
Me.lHeritage
Me.lRecon
Me.lChristmas

The dates on the UserForm is named similarly, but with pph instead of l.

 

[jim may]

Excel 2010

	A	B	C	D
August 9, 2019
is a FRIDAY
August 4, 2019
is a SUNDAY

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=WEEKDAY(DATEVALUE(B2),1)=1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=WEEKDAY(DATEVALUE(B5),1)=1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kakiebos]

Thank you Jim, but I need to do this with VBA as it is in a UserForm that I want to get the result. That is, if I change the year in the TextBox, "tbYear", I want all the Labels with public holiday names, that fall on a Sunday for that specific year to become red.

 

[FormR]

Hi, you could try like this:

If Weekday(Me.pphWomens & Me.tbYear = "09 August" & Me.tbYear) = vbSunday Then

 

[kakiebos]

Thank you MrExcel

I tried your code like this.

  'Test for Sunday
'  If Weekday(Me.pphNewYears & Me.tbYear = "01 January" & Me.tbYear) = vbSunday Then
  If Weekday(Me.pphNewYears & Me.tbYear = vbSunday) Then
    Me.lNewYears.ForeColor = &HFF&
  Else
    Me.lNewYears.ForeColor = &H80000008
  End If

I have commented your code out, because it kept the label black. Even if the date falls on a Sunday. I then tried to adapt that code, but this time the label is changed to red, but on all the days of the week.

In my UserForm, I have a label with the public holiday's name. In this code above New Year's day. The label's property name is "lNewYears". Next to the label with the holiday name is a label with the date that is formated "dd mmmm" this label is named "pphNewYears" When I press the OK button in the UserForm, all this is sent to a Table that will be used in Vlookup formulas in the rest of the spreadsheet. To update the Holidays in the Spreadsheet, I call the UserForm and change the year in the TextBox "tbYear"

Before all this is sent to the table in the spreadsheet, I need to know which holidays fall on a Sunday. To do this in a quick and visual way, I want the ForeColor of the label for that specific day to change to red. If it is not on a Sunday, but any other day of the week, the ForeColor of the label must stay black.

 

[FormR]

I think I rushed my first response.

Do you want to check that the date is the 9th August and it is a sunday?

If so..

If Me.pphWomens & Me.tbYear = "09 August" & Me.tbYear And Weekday("09 August" & Me.tbYear) = vbSunday Then

 

[kakiebos]

No The date is the 9th of August. I want to see if it is on a Sunday for the year specified in the Textbox above. The TextBox is labled tbYear.
Also sorry, that part of my code was actually where I checked if I can get my Idea to work. That is why I still have it like that.
So if the 9th of August in 2020 is on a Sunday, the label pphWomens must be red. In 2021 where it is on a Monday, the label must be Black.
In 2023 New Years is on a Sunday, so, if my I change the year in the textbox to 2023, the label for New Years must be red. If the holiday date is on a Sunday for that specific year, the corresponding label text (ForeColor) must change to red after update of the textbox.

 

[FormR]

It's all a bit confusing to be honest - but in principle if you want to check if a date is a Sunday use the weekday() function.

 

[kakiebos]

I came up with this piece of coding. I don't believe it is the best for my application, but it seems to work. If anyone can suggest code that will be better, I'm always open for suggestion.

  'Test for Sunday
  Dim strNewYear As String
  Dim strHumanRights As String
  Dim strFreedom As String
  Dim strWorkers As String
  Dim strYouth As String
  Dim strWomens As String
  Dim strHeritage As String
  Dim strReconcilliation As String
  Dim strChristmas As String
  Dim strBoxing As String
  
  strNewYear = Me.pphNewYears & " " & Me.tbYear
  If Weekday(CDate(strNewYear), vbSunday) = 1 Then
    Me.lNewYears.ForeColor = &HFF&
  Else
    Me.lNewYears.ForeColor = &H80000008
  End If
  
  strHumanRights = Me.pphHumanRights & " " & Me.tbYear
  If Weekday(CDate(strHumanRights), vbSunday) = 1 Then
    Me.lHumanRights.ForeColor = &HFF&
  Else
    Me.lHumanRights.ForeColor = &H80000008
  End If
    
  strFreedom = Me.pphFreedom & " " & Me.tbYear
  If Weekday(CDate(strFreedom), vbSunday) = 1 Then
    Me.lFreedom.ForeColor = &HFF&
  Else
    Me.lFreedom.ForeColor = &H80000008
  End If
  
  strWorkers = Me.pphWorkers & " " & Me.tbYear
  If Weekday(CDate(strWorkers), vbSunday) = 1 Then
    Me.lWorkers.ForeColor = &HFF&
  Else
    Me.lWorkers.ForeColor = &H80000008
  End If
  
  strYouth = Me.pphYouth & " " & Me.tbYear
  If Weekday(CDate(strYouth), vbSunday) = 1 Then
    Me.lYouth.ForeColor = &HFF&
  Else
    Me.lYouth.ForeColor = &H80000008
  End If
  
  strWomens = Me.pphWomens & " " & Me.tbYear
  If Weekday(CDate(strWomens), vbSunday) = 1 Then
    Me.lWomens.ForeColor = &HFF&
  Else
    Me.lWomens.ForeColor = &H80000008
  End If
  
  strHeritage = Me.pphHeritage & " " & Me.tbYear
  If Weekday(CDate(strHeritage), vbSunday) = 1 Then
    Me.lHeritage.ForeColor = &HFF&
  Else
    Me.lHeritage.ForeColor = &H80000008
  End If
  
  strReconcilliation = Me.pphRecon & " " & Me.tbYear
  If Weekday(CDate(strReconcilliation), vbSunday) = 1 Then
    Me.lRecon.ForeColor = &HFF&
  Else
    Me.lRecon.ForeColor = &H80000008
  End If
  
  strChristmas = Me.pphChristmas & " " & Me.tbYear
  If Weekday(CDate(strChristmas), vbSunday) = 1 Then
    Me.lChristmas.ForeColor = &HFF&
  Else
    Me.lChristmas.ForeColor = &H80000008
  End If
  
  strBoxing = Me.pphBoxing & " " & Me.tbYear
  If Weekday(CDate(strBoxing), vbSunday) = 1 Then
    Me.lBoxing.ForeColor = &HFF&
  Else
    Me.lBoxing.ForeColor = &H80000008
  End If