Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Icon Sets Examples in Excel
  • How to Create Gantt Chart in Excel
  • Excel Pie Chart
  • Remove Duplicates Example in Excel
  • How to calculate current stock or inventory in Excel

References

  • How to use Excel VLOOKUP Function
  • Multi-criteria lookup and transpose in Excel
  • How to get first column number in range in Excel
  • How to use Excel TRANSPOSE Function
  • How to use Excel ROWS Function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation date in specific year
  • Excel Data validation specific characters only

Tag: WEEKDAY function

WEEKDAY function: Description, Usage, Syntax, Examples and Explanation

by

What is WEEKDAY function in Excel? WEEKDAY function is one of Date and Time functions in Microsoft Excel that returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. Syntax of WEEKDAY function WEEKDAY(serial_number,[return_type]) The WEEKDAY function syntax has the following arguments: …

Continue Reading

Custom weekday abbreviation in Excel

by

This tutorial shows how to create custom weekday abbreviation in Excel using example below. To create a custom weekday abbreviation, you can use a formula based on the CHOOSE and WEEKDAY functions. With this approach, you can generate a custom one-letter abbreviation, two-letter abbreviation, or any weekday that makes sense in your particular situation. Formula =CHOOSE(WEEKDAY(date),”S”,”M”,”T”,”W”,”T”,”F”,”S”) …

Continue Reading

Count day of week between dates in Excel

by

This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.  Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6 …

Continue Reading

How to calculate next day of week in Excel

by

To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. Formula =date+7-WEEKDAY(date+7-dow) Explanation  In the example shown, the formula in D6 is: =B6+7-WEEKDAY(B6+7-2) Where B6 contains the date Friday, January 16, 2015, and 2 represents Monday, the “dow”. …

Continue Reading

How to calculate nth day of week in month in Excel

by

To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions. Formula =date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow) Note:  Day of week is abbreviated “dow” in the generic form of the formula. This is a number between 1 (Sunday) and 7 …

Continue Reading

Get work hours between dates custom schedule in Excel

by

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …

Continue Reading

Roll back weekday to Friday base on a particular date in Excel

by

Case study: If Monday, roll back to Friday To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. Formula =IF(WEEKDAY(date)=2,date-3,date) Explanation In the example shown, the formula in C5 is =IF(WEEKDAY(B5)=2,B5-3,B5) How this formula works The WEEKDAY function returns …

Continue Reading

Generate series of dates by weekends in Excel

by

This tutorials covers how to generates Series of dates increment by weekends from a single start date in Excel. If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. Formula …

Continue Reading

Calculate series of dates by workdays in Excel

by

This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function Formula =WORKDAY(date,1) Explanation   In the …

Continue Reading

Steps to create Dynamic calendar grid in Excel

by

This tutorial show how to  create Dynamic calendar grid in Excel using the example below. You can set up dynamic calendar grid on an Excel worksheet with a series of formulas, as explained in this article. Explanation of how this formula works In the example shown, the formula in B6 is: =start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6) where “start” is the named …

Continue Reading

Get last weekday in month in Excel

by

This tutorial shows you how to find the last weekday of the month in Excel. To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions. Formula =EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow) Explanation First, this formula determines the first …

Continue Reading

Conditional formatting gantt chart weekends in Excel

by

This tutorial shows how to work Conditional formatting gantt chart weekends in Excel using the example below; Formula =WEEKDAY(date,2)>5 Explanation To build a Gantt chart with weekends shaded, you can use Conditional Formatting with a formula based on the weekday function.In the example shown, the formula applied the calendar, starting at D4, is: =WEEKDAY(D$4,2)>5 Note: this formula deals …

Continue Reading

Highlight dates that are weekends in Excel

by

This tutorial shows how to Highlight dates that are weekends in Excel  using the example below; Formula =OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1) Explanation If you want to use conditional formatting to highlight dates occur on weekends (i.e. Saturday or Sunday),  you can use a simple formula based on the WEEKDAY function. For example, if you have dates in the …

Continue Reading

Excel Data validation allow weekday only

by

Using the example below, this tutorial shows how to use Excel Data validation allow weekday only. Formula =WEEKDAY(A1,2)<6 Explanation Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility. To allow a user to …

Continue Reading

Count dates by day of week in Excel

by

This tutorial shows how to Count dates by day of week in Excel using the example below; Formula =SUMPRODUCT(–(WEEKDAY(dates)=day_num)) Explanation To count dates by weekday (i.e. count Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function. In the example shown, the formula in F4 is: =SUMPRODUCT(–(WEEKDAY(dates,2)=G4)) Note: “dates” is the named …

Continue Reading

Sum by weekday in Excel

by

This tutorial shows how to Sum by weekday in Excel using the example below; Formula =SUMPRODUCT((WEEKDAY(dates)=day_num)*values) Explanation To sum data by weekday (i.e. sum by Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function. In the example shown, the formula in H4 is: =SUMPRODUCT((WEEKDAY(dates,2)=G4)*amts) How this formula works You might …

Continue Reading

OR function Examples in Excel

by

The OR function in Excel returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. Combine the OR function with other functions and become an Excel expert. 1. For example, take a look at the OR function in cell D2 below. Explanation: the OR function returns TRUE if the first score is …

Continue Reading

How to get Holiday Date from Year in Excel

by

This example teaches you how to get the date of a holiday for any year (2019, 2020, etc). Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of …

Continue Reading

How to get Weekdays, Working days between Two Dates in Excel

by

Learn how to get the day of the week of a date in Excel and how to get the number of weekdays/working days between two dates. Weekday Function 1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. Apparently, 12/18/2017 falls on a Monday. 2. You can …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • IF with boolean logic in Excel
  • How to use IFS function in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to show last updated date stamp in Excel
  • Add months to date in Excel
  • Convert Excel time to Unix time in Excel
  • How to calculate working days left in month in Excel
  • Add business days to date in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel

General

  • How to make excel worksheets print on one page?
  • Subtotal by invoice number in Excel
  • How to calculate percentage of total in Excel
  • Flash Fill in Excel
  • Using Existing Templates in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning