• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / WEEKDAY Function in Excel – Two Examples

WEEKDAY Function in Excel – Two Examples

The WEEKDAY function in Excel is one of its many wonderful date and time functions. These functions are some of the most popular in Excel.

The Excel WEEKDAY function returns a number that identifies the day of the week from a given date. It has several options to specify which day is the first day of the week.

This Excel function is very useful. This tutorial will show two examples of the Excel WEEKDAY function helping us to achieve typical Excel tasks.

WEEKDAY Function in Excel Anatomy

The syntax of the WEEKDAY function in Excel is as follows. It contains two arguments.

=WEEKDAY(serial number, [return type])

The serial number is simply the date that you want to return the weekday for.

The return type is a number from 1 to 7 that identifies the day of the week of the given date. It provides a list so that you can choose how you would like the number returned e.g. Sunday = 1 to Saturday = 7, or Monday = 1 to Sunday = 7.

Return type options for the WEEKDAY function in Excel

Using the WEEKDAY Function to Calculate Weekend Rates

One situation where you may want to use the WEEKDAY function is to calculate a different rate of pay for weekend work.

In the list below, the WEEKDAY function has been used with the IF function in column C to calculate a different rate of pay when working on a Saturday (these dates are in my local DMY format).

Calculate Saturday rate with the Excel WEEKDAY function

This was achieved my using the formula below in cell C2. This formula checks if the day of the week is equal to 7 (which indicates a Saturday). If this is true then the hours worked is multiplied by the Saturday rate, and if not then the hours worked is multiplied by the standard workday hourly rate.

=IF(WEEKDAY(A2,1)=7,B2*$F$3,B2*$F$2)

In this list, the 04/02/2017 and the 11/02/2017 were a Saturday. You can see that Excel applied the increased rate of pay.

This example can be seen in action on my Excel timesheet for different rates for shift work post.

Highlight the Saturday and Sundays in a List

In this second example, we will highlight the dates in a list that fall on a Saturday or a Sunday.

For this, we will need to use an Excel formula that includes WEEKDAY and the OR function within a Conditional Formatting rule.

  1. Select the range of dates you want to format.
  2. Click the Home tab, Conditional Formatting button and then New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the following formula in the box provided. Adjust the cell in the formula to the first cell of the selected range of cells.
=OR(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=7)

The following image shows the formatted weekend dates.

Highlight the weekend dates in a list in Excel

In this formula, the OR function is used to test more than one condition. Although a range of dates was highlighted, we only reference cell A2 in the formula because it is the first cell of that range.

The return type of 1 was used in the Excel WEEKDAY function. This means that the first day of the week is a Sunday.

If we had used return type 2, we could have written the following simpler formula. However, it is fun to explore other scenarios and to learn the OR function too.

=WEEKDAY(A2,2)>5

I used this technique of formatting weekend dates in my Excel Gantt Chart template.

More Excel Function Tutorials

  • Calculate how many Fridays between two dates
  • Excel formula for elapsed time in days, hours and minutes
  • 5 awesome date functions
  • Separate text into different cells
  • The TEXT function in Excel

Related Posts:

  • Custom sort in Excel
    Custom Sort in Excel
  • Moving average in Excel thumbnail
    Moving Average in Excel
  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value
  • Compare dates in Excel feature
    How to Compare Dates in Excel

Reader Interactions

Comments

  1. Angelo Fonseca says

    20 February 2017 at 1:39 pm

    Hi Alan,
    directly from Brazil, watching your videos. As usual, very clear and practical.
    Thanks,
    Angelo

    Reply
    • computergaga says

      20 February 2017 at 3:51 pm

      Your welcome Angelo. Thanks for the nice comment.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·