Suppose you need to determine which day of the week a date in the range from 1900 to 9999 corresponds to. Doing this in Excel is quite simple, but you will receive the serial number of the day of the week as an answer. In addition, depending on the settings, the number may correspond to different days. Of course, this is not very convenient. Therefore, let's create a module that will determine the day of the week by date and return not its serial number, but its name.

The day of the week allows you to define the "WEEKDAY" function, which can be entered by calling the function wizard and highlighting it in the list Category element Date and time (Fig. 6.1). The function syntax is as follows:

It returns the day of the week corresponding to the first argument, date_in_numeric_format. The day of the week will be represented as an integer in the range from 1 (Sunday) to 7 (Saturday).

The numeric_date argument is the date-time code that Excel uses for date operations. This argument can also be specified in text form, enclosing the string in quotes. Type is a number that defines the numbering of the days of the week. The Type argument can take the following values:

1 (or omitted) - a number from 1 (Sunday) to 7 (Saturday);
- 2 - a number from 1 (Monday) to 7 (Sunday);
- 3 is a number from 0 (Monday) to 6 (Sunday).

Thus, depending on the type specified in the WEEKDAY function, the 5th day of the week could be Thursday, Friday, or Saturday. To avoid specifying the second argument, we will assume that the 5th day of the week is Thursday.

Type in cell A1 any date, for example 01/06/2002. For clarity, set the date format for this cell. Go to cell A2 and call the “WEEKDAY” function panel (Fig. 6.2). In field Date_as_number enter cell address A1.

The DAY function returns the day that corresponds to a specific date. The result of the function is a number from the range from 1 to 31.

The WEEKDAY function returns the day of the week that corresponds to a specific date. The result of this function is a number from the range from 1 to 7 (one corresponds to Sunday, and seven to Saturday).

The DAYS function returns numeric value, characterizing the difference in days between two given dates.

Features of the syntactic notation of the function DAY, WEEKDAY and DAYS in Excel

The DAY function has the following syntax:

DAY(date)

Takes as input a single parameter, date, which corresponds to the date whose day you want to determine.

Notes:

1. The input parameter is usually a cell from an Excel table containing data in date format.
2. The results of calculations of the DATE function and other functions that return values ​​in Date format are also accepted as input.
3. When directly entering data between function brackets (for example, =DAY(“06/24/2018”)), you must use quotes, thereby indicating that the data passed to the input is of the Text type. Next, Excel automatically converts the data to Date format.
4. Error #VALUE! Will be generated if data of an unsupported Excel type was passed to the input of the DAY function, for example: DAY(“June 24-2018”) or DAY(“06/24/2018”).
5. The date parameter can be represented as a number in Excel time code.

The WEEKDAY function has the following syntax:

WEEKDAY(date,[type])

Description of function arguments:

1. Date is a required parameter corresponding to the date whose day of week you want to determine. This parameter has the features described in the notes for the previous function.
2. Type – a number in the range from 1 to 3, indicating which day of the week is considered the first (in some countries the first day of the week is Sunday, in ours it is Monday). If type=1, the first day of the week will be Sunday, type=2 – Monday. If type=3, the result of the WEEKDAY function will be a number in the range from 0 to 6, where 0 corresponds to Monday.

Note: in some cases, instead of the numbers 1,2...7, the name of the day of the week (Monday, Tuesday...Sunday) is required. To do this, you can create the following table:

And then withdraw text value, corresponding to the number of the day of the week:

However, for this task you can use the following function: =TEXT(A2,"dddd").

The result of this function is the text “Sunday”. If a short record of the day of the week (Sat, Mon, Wed) is required, “dddd” should be specified as the second parameter.

The DAYS function is used to calculate the number of days between two specified dates and has the following syntax: =DAYS(end_date, start_date).

Description of the arguments to this function:

1. End_date is a required parameter characterizing the end date of an event.
2. Start_date is a required parameter characterizing the start date of a certain event to perform the calculation.

Notes:

1. Calculating the number of days between dates converts the data to numbers in Excel time code. This means that the entries “=DAYS(“06/24/2018”;”06/13/2018”)” and “=DATEVALUE(“06/24/2018”)-DATEVALUE(“06/13/2018”)” will return the same values.
2. If one of the function's parameters represents a date written as text, it will be processed by the DATEVALUE function, which will return an integer date.
3. If the function's parameters are numbers whose values ​​are outside those allowed in the Excel time code, the #VALUE! error will be generated.
4. If the DAYS function uses parameters as data that cannot be represented as dates in Excel, the #VALUE! error will be returned.



Examples of calculations in Excel using the functions DAY, WEEKDAY and DAYS

The table contains several dates, recorded in Excel date format, on which employees of a certain enterprise should be paid wages. Due to changes in legislation, employee salaries must be calculated on the first day of each month. It is necessary to correct the dates from 07/15/2018 to 07/01/2018. Part of the table looks like this:

To solve, we use the following formula: =B3-DAY(B3)+1.

Let's fill in the remaining columns in the same way. As a result, we get a new column with the correct salary payment dates:

Determination of working days and weekends using the WEEKDAY formula

Example 2. The software product reads data from an Excel table and uses it in its calculations. One of the cells contains data in the Date format corresponding to the date the parcel was received. It is necessary to determine whether the specified date corresponds to a working day of the week (Mon to Fri) and return the logical value “true” or “false” (if it is a day off) for further processing by the program.

The original table looks like this:

To determine the day of the week, we will use WEEKDAY, the first argument of which is the data from the “Date of Arrival” cell, and the second is 2 (counting the days of the week starts from one, which corresponds to Monday). To solve we use the following function:

As you can see, all dates are working days of the week.

Why do you need the DAYS function in Excel?

Example 3. It is well known that the Second World War began on June 22, 1941 and ended on May 9, 1945. It is necessary to determine how many days the hostilities took place.

Let's enter the initial data into the table:

To calculate, we use the following formula: =DAYS(B3,A3).

We get the following result:

That is, hostilities lasted for 1417 days.

Note! Any date in Excel is the number of days that have passed since the date 01/01/1900. More details are described in the article: How to calculate a date in Excel? Therefore, you can freely use such a simple formula without functions: =B3-A3.

As you can see in the figure, the result is similar. In fact, the DAYS function in Excel is not needed at all.

This article contains several useful formulas that return a specific date.

Determination of the day of the year

January 1 is the first day of the year, and December 31 is the last. What about the rest of the days in between? The following formula returns the day of the year for the date stored in cell A1: =A1-DATE(YEAR(A1),1,0) . For example, if cell A1 contains the date February 16, 2010, the formula returns 47 because that date is the 47th day of the year.

The following formula returns the number of days remaining in the year since a specific date (assuming it is in cell A1): =DATE(YEAR(A1),12,31) .

Determining the day of the week

If you need to determine the day of the week for a date, the function WEEKDAY will cope with this task. The function takes a date as an argument and returns an integer from 1 to 7 corresponding to the day of the week. The following formula, for example, returns 6 because the first day of 2010 is on a Friday: =WEEKDAY(DATE(2010,1,1)) .

Function WEEKDAY also uses an optional second argument indicating the day numbering system for the result. If you provide 2 as the second argument, the function will return 1 for Monday, 2 for Tuesday, etc. If you provide 3 as the second argument, the function will return 0 for Monday, 1 for Tuesday, etc. .

You can also specify the day of the week for a cell containing a date by applying a custom number format. A cell using the following format displays the day of the week: DDDD. Keep in mind that the cell actually contains the full date, not just the day number.

Determining the date of the last Sunday

The formula in this section returns the last day specified. You can use the following formula to get the date of last Sunday. If the current day is Sunday, then the formula returns the current date. The result will be serial number dates (you need to format the cell to display a readable date): =TODAY()-REST(TODAY()-1;7) .

To modify this formula to find a date on a day other than Sunday, change the 1 to another number in the range 2 (Monday) to 7 (Saturday).

Determining the day of the week after a date

The following formula returns a specified day of the week that occurs after a specific date. For example, you can use this formula to determine the date of the first Friday after July 4, 2010. The formula assumes that cell A1 contains a date, and cell A2 contains a number from 1 to 7 (1 corresponds to Sunday, 2 to Monday, etc.): =A1+A2-WEEKDAY(A1)+(A2

If cell A1 contains July 4th. 2010, and cell A2 contains b (which represents Friday), then the formula returns July 9, 2010. This is the first Friday after July 4, 2010 (the day that falls on Sunday).

Finding the nth specific day of the week in a month

You may need a formula to find the date of a specific day of the week. Let's say your company's payday is on the second Friday of every month and you need to determine these
payment days for each month of the year. The following formula will do the required calculation:
=DATE(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+(A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7

This formula assumes that:

• cell A1 contains the year;
• cell A2 contains the month;
• cell A3 contains the day number (1 - Sunday, 2 - Monday, etc.);
• cell A4 contains a number - for example 2, indicating the second occurrence of the day of the week specified in cell A3.

When using this formula to determine the date of the second Friday in June 2010, the result would be June 11, 2010.

Determining the last day of the month

To determine the date that corresponds to the last day of the month, you can use the function DATE. However, you need to increment the month by 1 and set the day value to 0. In other words, the "0th" day next month- This is the last day of the current month.

The following formula assumes that the date is stored in cell A1. The formula returns the date that corresponds to the last day of the month: =DATE(YEAR(A1),MONTH(A1)+1,0) .

You can modify this formula to determine how many days a given month includes. The following formula returns an integer that corresponds to the number of days in the month for the date in cell A1 (make sure you format the cell as a number and not as a date): =DAY(DATE(YEAR(A1),MONTH(A1)+1, 0))

Determining the quarter of a date

For financial statements, presenting information by quarter may be useful. The following formula returns an integer between 1 and 4 that corresponds to the calendar quarter for the date in cell A1: =ROUND UP(MONTH(A1)/3,0) . This formula divides the month number by 3 and then rounds the result.

Good day, friends!

I devoted this article to the topic of how to determine the day of the week by date in Excel. Very often this question is faced with wages and HR department specialists, although there are quite a lot of solutions to questions when it is necessary to know whether a given date falls on a working day of the week or not. These tasks include calculating sick leave, determining holidays, determining vacation days, and, indeed, many more tasks where you need to determine the day of the week in Excel by date.

There are several ways to determine the day of week, and in this article we will look at 3 options:

Using a custom format

A very simple way to determine the day of the week by date is done as follows:

• select the ones in which you want to make changes in the date display;
• call the context menu with the right mouse button and select the item "Cell Format";
• in the window that appears, select a tab "Number", In chapter "Number Formats" you need to select an item "(all formats)" and enter the date format you need in the Type field.

The data formats themselves can be divided into 2 types: full And abbreviated.

• Full format designated full name day of the week or month (for example, Wednesday, Thursday, etc.), the full format is indicated “DDDD” for days of the week, “MMMM” for months.
• Short format is displayed in only two letters (for example, Wed, Thu, Fri, etc.) and for this purpose the format “DDD” is indicated for days of the week, “MMM” for months.

Using the TEXT function

In this case, we will use the built-in to determine the day of the week by date in Excel. This function is designed to format a number in a cell and convert it to text.
Using this function, you specify the data source as the first value, and then specify the data conversion format. The format you specify abbreviated or full, as I described above in the first paragraph.

The spelling of the formula looks like this:

=TEXT (A1;"dddd") , the formula returns the text value of the day of the week.

Using the WEEKDAY function

The third option to find out the day of the week by date is using the WEEKDAY function, which belongs to the group "Date and time", there you will find it. This function returns a number from 1 to 7, which corresponds to the day of the week.
The formula looks like this:

=WEEKDAY(A1,2) , Where:

• "A1» – this is the address of the cell where the date is in numeric format,
• "2» – this is a number that determines the type of the return value (there are 3 types: 1 or none – returns a number from 1 (Sunday) to 7 (Saturday), 2 – from 1 (Monday) to 7 (Sunday) and 3 – from 0 (Monday) to 6 (Sunday), we are more accustomed to only type 2).

I think you will find the article “” interesting in it you will find useful tips and examples when working with dates.

And that's all for me! I really hope that that the topic of how to determine the day of the week by date in Excel has been covered and has become useful to you . I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!

To create charts or do other analysis, you sometimes need to display the day of the week in Excel in a date cell in a spreadsheet editor. There are several ways to do this. We will describe each of them separately.

In this case, using functions built into Excel, the entered data in date format is converted to the day of the week. First you need to enter information in the cell: . By right-clicking on a cell, a context menu will appear in which you need to select the “Format” item. In the window that appears, on the NUMBER tab, you need to select the line “all formats” from the proposed options.

Next, in the right window you will need to enter the value DDDD. The “Sample” field will automatically display the day of the week corresponding to the previously entered date. In the editor, the contents of the line have changed, but if you hover over it and click left button mouse, only the date will be displayed in the formula bar. You can also take the DDD string as a formatting type, which converts the content into an abbreviated name of the day of the week (Tuesday - Tue, Wednesday - Wed, etc.)

Option #2: Using the TEXT function to get the day of the week in Excel

The Excel editor, in combination with the necessary formatting functions, is a universal tool for working with tables. To convert a date, enter it into the selected cell and select any free area on the sheet. To the left of the “Formula Bar” field is the “Insert Function” button. After clicking on it with the left mouse button, a settings window will appear:

• You need to select “Text” from the list of categories.
• In the list of available functions, select the option called TEXT and press OK to proceed to entering arguments.
• In the parameters there are two fields to set: value and format. The address of the cell with the date is entered as the first one. This can be done manually or by placing the cursor in the desired location and clicking with the left mouse button. After this, the address should automatically appear in the “Value” line.
• In the “Format” field you will need to enter the expressions DDDD or DDD (for standard text abbreviation) and agree with the arguments.

Thus, two values ​​of the same type appear on the sheet, but in different formats: the date and the name of the day of the week corresponding to it. This method allows the data to be used in a compatible manner. When the date is changed, the day of the week value is also converted. You can manually set functions in the formula bar to speed up the editing process: =TEXT(A1,“DDDD”).

Option #3: Inserting the WEEKDAY function

This function is similar to the previous one, but converts a text value to a numeric value. In this case, the user has advanced options: setting the start of the countdown to one of two selected days - Monday and Sunday. To enter you need to go through the following procedure:

• Select the cell in which the converted value will be placed.
• Enter the Function Wizard settings and select the “Date and Time” line from the category catalog.
• From the list presented, you will need the WEEKDAY function, the arguments of which are “date in numeric format” and type.
• As the first one, you need to enter the required date DD.MM.YYYY. For convenience, you can specify the address of the cell with the corresponding content.
• The TYPE argument takes three values: 1, 2, 3. If you specify one, the days of the week will be counted from Sunday. Two means Monday. Three - the countdown starts from Monday, which will be accepted by the counter as “0” (that is, Tuesday will be displayed as “1”).

To enter manually in the formula bar, you need to write: =WEEKDAY(A3,2). It is not necessary to specify the TYPE parameter. If it is absent, the editor sets the default value to “1”. This is true for Great Britain and other English-speaking countries, where the week begins on Sunday. In the CIS countries, you will need to enter the parameter yourself each time you activate the function.

The use of operators is due to the wide range of functionality, because when the date value changes, the contents of the cell with the function are also converted. When working with tabular databases, this is the most optimal method for converting formats to obtain the day of the week value in Excel.

Share our article on your social networks: