VBA: If date in one column is a weekend Then...

[fadetograham]

Hi everyone

This has been driving me around the bend and have decided to throw it out into the forum as I've tried numerous connotations, searched high and low for a solution and can't crack it.

What I'm trying to do is populate the cell in column N with 8:00 if the date in column A of that row is a weekend.

The date in column A is in dd/mm/yyyy format.

e.g.
Private Sub wknd()
Dim i As Integer

For i = 6 To 1000
If Cells(i, 1).Value = Weekday("7") Or Weekday("1") Then
Cells(i, 14) = "8:00"
End If
Next

End Sub

This is functioning but inserting 8:00 into every cell regardless of the date in cell (i, 1).

I'm probably missing something small but I just can't find the solution so any help would be really appreciated.

Thanks
Graham

 

[Jonmo1]

Try

If Left(Format(Cells(i, 1).Value,"ddd"),1) = "S" Then

 

[Rick Rothstein]

Hi everyone

This has been driving me around the bend and have decided to throw it out into the forum as I've tried numerous connotations, searched high and low for a solution and can't crack it.

What I'm trying to do is populate the cell in column N with 8:00 if the date in column A of that row is a weekend.

The date in column A is in dd/mm/yyyy format.

e.g.
Private Sub wknd()
Dim i As Integer

For i = 6 To 1000
If Cells(i, 1).Value = Weekday("7") Or Weekday("1") Then
Cells(i, 14) = "8:00"
End If
Next

End Sub

This is functioning but inserting 8:00 into every cell regardless of the date in cell (i, 1).

You have to apply the WeekDay function to the date and check if it returns 1 or 7 (as a number, not a text string)...

If WeekDay(Cells(i, 1).Value) = 7 Or WeekDay(Cells(i, 1).Value) = 1 Then

There is a slight shortcut for this if you use the optional second argument for the WeekDay function. The following line of code is equivalent to the one I just gave you above...

If WeekDay(Cells(i, 1).Value, vbMonday) > 5 Then

 

[fadetograham]

Thank you both!

I don't think I would have stumbled on to either of those solutions no matter how much time I spent on it.

Thanks again