weekday name formula in excel monday to friday

"Excel: Calculate Weekday Name (Monday-Friday) Formula"

By Mark Young
AD

When working with Excel, staying organized often involves understanding the day of the week for each date. However, manually keeping track of weekdays can be cumbersome, leading toопи and errors. Fortunately, Excel offers a simple solution: a built-in function allowing you to create a weekday name formula from Monday to Friday.

This guide will walk you through creating and optimizing this quick and essential formula, ensuring you stay productive and accurate in your Excel tasks.

Understanding Excel's Weekday Function

The Weekday function in Excel returns the day of the week as a number, representing Monday through Friday. By default, it considers 1 as Sunday, 2 as Monday, and so on until 7, which is Saturday. To get the desired output (Monday to Friday), we need to adjust this default.

AD
weekday formula in excel monday to friday
weekday formula in excel monday to friday
Turn Dates Into Day Names Fast in Excel
Turn Dates Into Day Names Fast in Excel
Get next day of week
Get next day of week

Let's start by understanding the basic weekday name formula syntax: WEEKDAY_serial_number[,mode]. The mode argument allows us to customize the day 1 starting point.

Adjusting Day 1 Starting Point

To make Monday the first day of the week, set the mode argument to 21. This adjustment desplaces the day numbers so that Sunday is 1, Monday is 2, and the rest follow accordingly, with Friday becoming 6.

the excel formulas chart with numbers and symbols for each item in this table, you can
the excel formulas chart with numbers and symbols for each item in this table, you can
the basic excel formula is shown in green and white, as well as other numbers
the basic excel formula is shown in green and white, as well as other numbers
Top 21 Excel Formulas
Top 21 Excel Formulas

For example, using Weekday formula as =WEEKDAY(A2,21) will return the day of the week for the date in cell A2, with Monday as day 1 and Friday as day 6.

Formatting the Output

While Weekday function gives the correct numbers, you might want to display the weekdays as names (e.g., "Monday," "Tuesday," etc.). To do this, we'll use Excel's Text function or abstract name formulas.

AD

Using TEXT function, type '=TEXT(A2,"dddd")' where A2 is your date cell. This will return the weekday name. Alternatively, you can use simple formulas like =CHAR(83)`, `=CHAR(74)`, etc., where CHAR(83) stands for "M" for Monday, and so on. This way, "MOn" will become "Mo"nday and "Tue"sday.

10 Essential Excel Formulas You Should Know | Excel Tips & Tricks
10 Essential Excel Formulas You Should Know | Excel Tips & Tricks
Excel formulas
Excel formulas
ms excel formula
ms excel formula
Top 21 Excel Formulas Every Professional Should Know
Top 21 Excel Formulas Every Professional Should Know
the 50 excel formulas are shown in green and white, with text below them
the 50 excel formulas are shown in green and white, with text below them
Exel Formulas
Exel Formulas

Automatically Generating Weekday Names

Sometimes, you might need an entire column of weekday names for a range of dates. To achieve this, you can use a simple trick with the HLOOKUP function. Place Monday to Friday (as numbers) in a range and use the HLOOKUP function to fetch the corresponding weekday name from the column.

First, create a list of weekdays using numbers (2 to 6 or 1 to 5 depending on your initial day 1 setting). Then apply the formula =HLOOKUP(B2,$F$1:$G$7,2 FALSE) where B2 is your first date and F1:G7 are weekdays in numbers. Drag this formula down to get the required list.

AD

Creating a Weekday Name Formula for Specific Currency Formats

In some currencies, Monday is considered the first day of the week. To adapt the formula to these requirements, use different parameters in the Weekday and HLOOKUP functions to match the local conventions.

the excel must know formulas for each type of text, including numbers and symbols
the excel must know formulas for each type of text, including numbers and symbols

For instance, if Saturday is the first day of the week (day 1 = 2), use the Weekday formula as =WEEKDAY(A2,22). You'll need to adjust theTEXT or CHAR formula or lookup table in the HLOOKUP function similarly.

Optimizing your work with these weekday name formulas and techniques will make your Excel tasks more efficient, enhancing accuracy and readability. Happy calculating!"

AD
AD