Formula or code to identify a string as a weekend day

[wpryan]

I've been searching for a solution for a silly thing nagging me... I want to be able to identify a certain day of the week with a string... the string should be today's year, today's month, and a number in a certain cell. I started off just trying to get the date correct, but it's not working. Here's what I've done so far...

Range("AB14") = Format(Year(Date) & Month(Date) & Range("AB11").Value, "yymmdd")

if I put in range AB11 the number 27, then this should return a date of 15-05-27 (today's date), but instead gives a date of 28-12-93. Where am I going wrong?

 

[Mike LH]

Hi,

Try it like this

With Range("AB14")
    .Value = DateSerial(Year(Date), Month(Date), Range("AB11").Value)
    .NumberFormat = "yymmdd"
End With

 

[wpryan]

Thanks, it's working. I was further trying to get the day of the week but got stuck... I added this:

If Range("AB14").Value = vbWednesday Then
        MsgBox ("Did you really work today?"), vbOKOnly
End If

...the date in AB14 being Wednesday. The code is skipping through, being ignored.

 

[Mike LH]

Hi,

Try this

If Weekday(Range("AB14").Value) = vbWednesday Then
        MsgBox ("Did you really work today?"), vbOKOnly
End If