How to determine if a date falls on the weekend?

Question

Given a date as input, how can I determine whether the day falls on a weekend?

Answer 1

This is the most direct way to determine if MyDate falls on the weekend:

MsgBox Weekday(MyDate, vbMonday) > 5

The Weekday() function has an optional 2nd parameter that tells it which day of the week the week starts on. The Weekday() function returns an integer from 1 through 7.

I've instructed it to start the week on MONDAY and so SATURDAY would be 6 and SUNDAY would be 7.

Answer 2

Or, you can use this:

OR(MOD(WEEKDAY(cell), 7)=0, MOD(WEEKDAY(cell), 7)=1)

or

MOD(WEEKDAY(cell), 7) < 2

as a formula.

since 1 is Sunday and 0 is Saturday

Answer 3

There is a Weekday function that takes a Date as an argument and returns the day (1, 2, 3, etc.)

The return values are:

vbSunday (1)  
vbMonday (2)  
vbTuesday (3)  
vbWednesday (4)  
vbThursday (5)  
vbFriday (6)  
vbSaturday (7)  

Assuming that weekends are Saturday and Sunday, the function would look like this:

Public Function IsWeekend(InputDate As Date) As Boolean
    Select Case Weekday(InputDate)
        Case vbSaturday, vbSunday
            IsWeekend = True
        Case Else
            IsWeekend = False
    End Select
End Function

Answer 4

Formula for converting date to week days

Selection.FormulaR1C1 = "=TEXT(WEEKDAY(R7C" + CStr(i) + ",1),""ddd"")"

this will return day

eg:- if input is 02-may-2013 then it will return Thu --> (thursday) here variable i represents another column