Finding the Day of the Week in Excel

How To Find Day Of Week In Excel 1

How To Find Day Of Week In Excel ? In Excel, there are two types of day of the 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.

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

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

How To Find Day Of Week In Excel Using formatting to get the day of the week

Custom formatting can be used to show the day of the week. In the example, we want to show the day of the week the 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

                a. For the full weekday name enter dddd = Sunday

                b. For the abbreviated weekday name enter ddd = Sun

         5. Click OK

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

Using the WEEKDAY function:

How To Find Day Of Week In Excel ? The WEEKDAY Function is recommended when entering other formulas or embedding the WEEKDAY Function 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])

The serial number is a number assigned to the date in which you are trying to find the day of. 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 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 placed on the days of the 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 work week to start on Friday, you’d choose return type 15.

Return Type Codes:

How To Find Day Of Week In Excel 2

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)

            3. Hit enter and copy down Column D

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

How To Find Day Of Week In Excel 3

Embedding in a Formula:

How To Find Day Of Week In Excel 4

In the worksheet, we are estimating the delivery date using the 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 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 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 and when it hits 7, it counts from 1 again resulting in a 5.

Using the VLOOKUP 

VLOOKUP gives us an alternative to find the day of the week too. This is good when we are trying o 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 the week 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 WLOOKUP 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 in to the actual formula itself!

That’s the great thing about the CHOOSE formula, it essentially allows us to build a list and then set then the first argument in the formaul is an index number which 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, the CHOOSE formulas will pick the 4th entry from our list.

Let’s put it in to action:

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

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

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