In todays Microsoft Excel quick tip we are going to make use of the WEEKDAY( ) function in excel to determine if a date is a weekday or weekend. This has many practical uses when examining time series data. In order to use the WEEKDAY( ) function in a formula I am going to use the IF( ) function and the OR( ) function.

The WEEKDAY( ) Function

The WEEKDAY( ) function is a simple function that takes two parameters and returns an integer from 1-7 as  the result.

WEEKDAY(serial_number, return_type)

serial_number

You may be wondering what a serial number is, by default Excel stores dates as serial numbers January 1st 1900 has the serial number 1. For this reason you must ensure that dates you wish to look at are formatted as excel dates and not text.

return_type

The return type simply specifies the integer range that the WEEKDAY() function will return. If you do not specify a return type or use return type 1 the days of the week will start at Sunday:

• Sunday = 1
• Monday = 2
• Tuesday = 3
• Wednesday = 4
• Thursday = 5
• Friday = 6
• Saturday = 7

In this case if WEEKDAY( ) returns 1 or 7 we know it is a weekend. Knowing this we can write a simple formula to test to see if a range of dates correspond to a weekday or weekend.

Step 1

Setup a range of dates in column A2. Ensure the cells are formatted as dates.

Step Two

In cell A2 we will write our formula using the IF( ), OR( ) and WEEKDAY( ) functions. In cell A2 type the following formula:

=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),”Week End”, “Week Day”)

This function will return true if it is a Saturday OR Sunday and the appropriate string will be inserted into the cell. Fill down for your remaining date range.