How To Find Day Of Week In Excel

Is Finding The Excel Day Of Week Easy? 

In Excel, there are two types of day of week functions. The first is if you have a date (8/19/17) and you want to know the day of the week (Saturday). We can achieve this result by customizing the date format for the cell or another cell within the worksheet. Basically, we use the day of the week calculator in a sense.

The second is the WEEKDAY function, it will also yield the day of the week excel based upon the date. The difference between the two is the WEEKDAY function will return a numeric value that represents the day of week excel.

Using the WEEKDAY function, 8/19/17 would yield a result of 17 for Saturday.

Using Formatting To Get The Day Of The Week Excel

Custom formatting can be used to show the day of the week excel. In the example, we want to show the excel day of week order shipped.

1. In cell C2 enter = B2 to easily copy the Ship Date over into the new column, copy down Column C.
2. Highlight the data in Column C and Right-Click, Select Format Cells from the menu.
3. Go to Custom in the Category Section.
4. Enter the Type of formatting you wish to display
5. For the full weekday, name enter dddd = Sunday
6. For the abbreviated weekday, name enter ddd = Sun
7. Click OK

Now as displayed above, Column C will have the day of the week excel that the order shipped.

Using the Excel WEEKDAY Function:

The excel WEEKDAY Function is recommended when entering other formulas or embedding the WEEKDAY Function excel within a formula. The function can be found under the Date & Time Button in the Formulas Tab.

The Formula or Syntax for the WEEKDAY Function is:

= WEEKDAY (serial_number, [return_type])

A serial number is a number assigned to the date on which you are trying to find the day. To manually enter a date and translate the date into a serial number, embed the DATE function.

DATE is written as =DATE (year, month, day) and embedded into the WEEKDAY Function excel would be:

= WEEKDAY (DATE (year, month, day))

Or

= WEEKDAY (DATE (2017, 8, 19))

To find the day of the week for August 29, 2017

If the date is listed within the worksheet or another worksheet, you simply have to enter the cell number of the date as the serial number. Keeping with the example:

= WEEKDAY (B2)

would give the same calculated result of 7 as manually entering the date.

The return type tells Excel what numerical value you would like to be placed on the excel days of week.

Basically, you choose the weekday that starts your week by choosing the return type that places a 1 for that weekday. If you want your workweek to start on Friday, you’d choose return type 15.

Return Type Codes:

To find the numerical value of the workday:

In the example, our week starts on Sunday so we will omit the return_type and the date we are using is listed in Column B;

1. In cell D2, enter the WEEKDAY Function
2. = WEEKDAY (B2)Hit enter and copy down Column D

Tip: You can quickly change the formatting to the custom format as discussed above to display the excel day of week (i.e. Saturday).

Embedding in a Formula:

In the worksheet, we are estimating the delivery date using the excel WEEKDAY Function and an IF Function.

All 2-day shipping will be delivered 2 days after shipping and Standard will be delivered 5 days after shipping.

1. To figure Standard shipping = WEEKDAY (WEEKDAY (B2) +5)
2. To figure 2-day Shipping = WEEKDAY (WEEKDAY (B2) +2)
3. Now embed those two into an IF Function = IF (D2= ”Standard”, WEEKDAY (WEEKDAY (B2) +5), WEEKDAY (WEEKDAY (B2) +2))
4. This statement literally reads, If the shipping type is Standard, add 5 days to the ship date and display as 1 to 7 with Sunday being 1, if not add 2 days to the ship date and display as 1 to 7 with Sunday being 1.

Note: Entering only one excel WEEKDAY Function into the formula may yield a result of higher than 7, but by embedding that function into another WEEKDAY Function Excel will change the calculated result back to a weekday numerical value.

For example, In Row 3, Saturday = 7 and Standard = 5 so the result is 12. Embed that within another WEEKDAY Function excel and when it hits 7, it counts from 1 again resulting in a 5.

Note: Entering only one WEEKDAY Function into the formula may yield a result of higher than 7 but by embedding that function into another WEEKDAY Function Excel will change the calculated result back to a week-day numerical value.

For example, in Row 3, Saturday = 7 and Standard = 5 so the result is 12. Embed that within another WEEKDAY Function excel and when it hits 7, it counts from 1 again resulting in a 5.

Using the VLOOKUP 

VLOOKUP gives us an alternative to finding the day of week excel too. This is good when we are trying to associate a number with a day. i.e. the number ‘one’ (1) is ‘Monday’, ‘two’ is Tuesday, etc.

Let’s say we have delivery days of products in a system (which is like working day + 3, 4, etc from accounting). Our challenge is to understand what actual day of the week that represents, then we can simply create a quick table to find corresponding days of the week.

So, we can write a VLOOKUP formula to look up the day of week excel for us

= VLOOKUP (B3, $G$2:$H$9, 2, 0)

We drag it down and end up with our list

Using the CHOOSE function

Another creative but slightly cheating method is to use the CHOOSE function (or formula). This is great if we want to achieve the exact same outcome as above from the VLOOKUP approach but without the hassle of a table in the worksheet making our work look messy. In this case, we build the list of days into the actual formula itself!

That’s the great thing about the CHOOSE formula, it essentially allows us to build a list and then set the first argument in the formula, which is an index number that tells the formula to pick the nth value from the list. So, if the cell you are referring to has the value 4 in it, then CHOOSE formulas that will pick the 4th entry from our list.

Let’s put it into action:

= CHOOSE (B3, “Monday”, “Tuesday”, “Wednesday”, ” Thursday”, “Friday”, “Saturday”, “Sunday”)

Press enter, drag the formula down and we will get the same outcome as before but with no extra table lurking in our worksheet.

When working with CHOOSE, Just be careful not to enter a number that is outside of the list otherwise, you will get an error.