# Excel/VBA Weekday Function

The Weekday function returns the number of day of the Week for the given date.

**What is Weekday function in VBA?**

The Weekday function returns the number of days of the Week for the given date.

For example, we want to know what is Weekday (Sat, Sun, Mon, etc.) for the date 1^{st} Jan 2000.

We can pass this date to the **Weekday() function** and it returns the number of day as a whole number.

**Syntax:**

Weekday(date, [ firstdayofweek ])

The last section of the tutorial shows the usage of **firstdayofweek** argument.

**An example of using Weekday function in VBA**

For the example, we are using the same date as given above i.e. 1^{st} Jan 2001.

See what we get after using the Weekday function and displaying the returned value in a message box:

Sub Date_ex() Dim ChkDate, WkDay 'Checkign the date for number of day in the Week ChkDate = #1/1/2000# WkDay = Weekday(ChkDate) MsgBox ("Number of day for 1st Jan 2000: " & WkDay) End Sub

**Output:**

**How VBA treats the number of days?**

VBA has meaning for the return values as follows:

Constant | Value | Description |
---|---|---|

vbSunday | 1 | Sunday |

vbMonday | 2 | Monday |

vbTuesday | 3 | Tuesday |

vbWednesday | 4 | Wednesday |

vbThursday | 5 | Thursday |

vbFriday | 6 | Friday |

vbSaturday | 7 | Saturday |

As we got 7 in our above example, so it was Saturday.

**Using Select/Case statement in VBA to get day name for day number**

In the following example, we are using VBA Select Case statement, so that a day name is displayed based on the given date.

As such, it may be hard to remember which number belongs to what day's name.

**Code:**

Sub Date_ex() Dim ChkDate, WkDay 'Checkign the date for number of day in the Week ChkDate = #1/1/2000# WkDay = Weekday(ChkDate) Select Case WkDay Case 1 MsgBox "Sunday On: " & ChkDate Case 2 MsgBox "Monday On: " & ChkDate Case 3 MsgBox "Tuesday On: " & ChkDate Case 4 MsgBox "Wednesday On: " & ChkDate Case 5 MsgBox "Thursday On: " & ChkDate Case 6 MsgBox "Friday On: " & ChkDate Case 7 MsgBox "Saturday On: " & ChkDate Case Else MsgBox "No Way!" End Select End Sub

**Output:**

**Using second argument: firstdayofweek**

If you want to change the first day of the week other than Sunday (default) then you may specify it by using the **firstdayofweek** argument.

It has the following possible values:

Constant | Value | Description |
---|---|---|

vbUseSystem | 0 | Use the NLS API setting. |

vbSunday | 1 | Sunday (default) |

vbMonday | 2 | Monday |

vbTuesday | 3 | Tuesday |

vbWednesday | 4 | Wednesday |

vbThursday | 5 | Thursday |

vbFriday | 6 | Friday |

vbSaturday | 7 | Saturday |

In the example below, we will make Monday as the first day of the Week.

**Code:**

Sub Date_ex() Dim ChkDate, WkDay ChkDate = #1/1/2000# 'Making Mon as 1, Tue = 2,... Sat = 6 WkDay = Weekday(ChkDate, vbMonday) MsgBox ("Number of day for 1st Jan 2000: " & WkDay) End Sub

**Output:**

You can see, we used the same code as in the first example except used the second argument and we got 6 for Saturday unlike 7 in the first example.

**Using Excel cells to get the number of day in the week**

To make things more clear about Weekday function and particularly, its second parameter, we will use a range of cells from A2 to A6 in an Excel sheet.

We have different date formats in the cells.

In VBA code, we used a For..Each loop. In each iteration, the Weekday() function is used to get the number of day.

The returned result is assigned to adjacent B, C, and D cells.

- B2:B6 = Sunday as 1
- C2:C6 = Monday as 1
- D2:D6 = Friday as 1

**See the code and output below:**

Sub Date_ex() Dim Rng As Range Dim x 'Setting our range of cells in Excel Sheet Set Rng = Range("A2:A6") x = 2 'Using for each loop to get through each A2 to A6 cells and get the number of day For Each cell In Rng Range("B" & x) = Weekday(cell) Range("C" & x) = Weekday(cell, vbMonday) Range("D" & x) = Weekday(cell, vbFriday) x = x + 1 Next End Sub

**Output:**