Excel function determining the day of the week. How to add and subtract dates, days, weeks, months and years in Excel. Determining the day of the week using the "day of the week" function

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

Determining 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 contained in cell A1): = DATE (YEAR (A1); 12; 31).

Determining the day of the week

If you need to define the day of the week for a date, the function DAY WEEK 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 Friday: = WEEKDAY (DATE (2010; 1; 1)).

Function DAY WEEK also uses an optional second argument that specifies the day numbering system for the result. If you specify 2 as the second argument, then the function will return 1 for Monday, 2 for Tuesday, and so on. If you specify 3 as the second argument, the function will return 0 for Monday, 1 for Tuesday, and so on. ...

You can also define the day of the week for a cell containing a date using a custom number format. A cell using the following format displays the day of the week: DDDD... Keep in mind that the cell does indeed contain the full date, not just the day number.

Determining the date of the last Sunday

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

To change this formula to find the date on a day other than Sunday, change 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 after a specified date. For example, you can use this formula to determine the date for the first Friday after July 4, 2010. The formula assumes that cell A1 contains the 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), 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. Suppose your company's payday falls 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 perform 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 number of the day (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 you use this formula to determine the date for the second Friday in June 2010, the result is June 11, 2010.

Determining the last day of the month

To determine the date that the last day of the month corresponds to, you can use the function DATE... However, you need to increment the month by 1 and specify day 0 as the value. In other words, the "0" day of the next month 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 specified month includes. The following formula returns an integer that represents the number of days in the month for the date in cell A1 (make sure you format the cell as a number, not a date): = DAY (DATE (YEAR (A1); MONTH (A1) +1; 0))

Defining the quarter of a date

For financial statements, it may be helpful to present information on a quarterly basis. The following formula returns an integer from 1 to 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.

Consider here, how to convert date to day of week in Excel... This technique is useful when drawing up a calendar, letterhead, timesheet, etc. We have a table. Column B contains dates. In column A, we want to write the days of the week (Mon, Tue, etc.). Set the dates in column A.
Now, in column A, replace the date numbers with the day of the week. Select the cells in column A (A3: A10). We go into the format of cells, select the function "All formats".
In the "Type" line of the "Format Cells" dialog box, write the following format - DDD.
Click "OK". It turned out like this.
This method can be applied in the timesheet. In the line with dates, set the date format - only number (D). Then, in the cell we will write the entire date, for example - 01/12/2016. And in a cell with this format only the number will be visible - 12. It is enough to set this format in the first cell, write the date in it. Then, drag this cell along a column or row, and, at the same time, the date format is copied and the cells are filled with consecutive dates.
For the date formats you can set, see How to Create a Custom Format in Excel.
The form of the timesheet can be viewed and downloaded in the article "Timesheet in Excel".
How to determine the quarter number, the beginning and the end of the quarter by date, read in the article "Quarter number in Excel".
How to determine the date by the day of the week or, for example, the date of the third Sunday in March 2016, etc., see the article "Find out the date in Excel by day of the week, etc." ... In this way, you can find out the date and the future and the past.
How to add dates, add days, months, years to a date, how to calculate seniority by dates, age, etc., see the article "Date in Excel. Formula".
In Excel, you can quickly move a column, table, row. How to do this, see the article "

When working in Excel, the task is sometimes posed so that after entering a specific date in a cell, the day of the week that corresponds to it is displayed. Naturally, to solve this problem with such a powerful table processor like Excel, perhaps in several ways. Let's see what options exist for performing this operation.

There are several ways to display the day of the week by the date entered, from formatting cells to applying functions. Let's take a look at everything existing options performing the specified operation in Excel so that the user can choose the best one for a specific situation.

Method 1: apply formatting

First of all, let's see how you can display the day of the week by the entered date using cell formatting. This option involves converting the date to the specified value, rather than keeping the display of both types of data on the worksheet.

In field "A type" format windows instead of value "DDDD" you can also enter the expression:

In this case, the abbreviated name of the day of the week will be displayed on the sheet.

Method 2: using the TEXT function

But the method presented above involves converting a date to a day of the week. Is there an option for both of these values to be displayed on the sheet? That is, if we enter a date in one cell, then the day of the week should be displayed in another. Yes, there is such an option. It can be done using the formula TEXT... In this case, the value we need will be displayed in the specified cell in text format.

Moreover, if you change the date value in a cell, the day of the week will automatically change accordingly. Thus, by changing the date, you can find out on what day of the week it will fall.

Method 3: using the WEEKDAY function

There is one more operator that can display the day of the week for a given date. This is the function DAY WEEK... True, it displays not the name of the day of the week, but its number. In this case, the user can set from which day (from Sunday or from Monday) the numbering will be counted.

As with the previous function, changing the date automatically changes the day of the week in the cell where the operator is set.

As you can see, Excel has three main options for representing the date as a day of the week. All of them are relatively simple and do not require the user to have any specific skills. One is to use special formats, and the other two use built-in functions to achieve these goals. Considering that the mechanism and method of displaying data in each described case are significantly different, the user must choose himself which of the indicated options in a particular situation suits him best.

The day of the week, namely "Monday", "Tuesday", "Wednesday" and so on, can be obtained in Excel using a formula by date. That is, when there is a date and it is necessary that the names of the day of the week appear next to the cell, you can do this with a formula. Moreover, on the Internet there are several formulas for this. We will not consider them, we will give only one (in our opinion, the most optimal), so

Formula day of week by date

TEXT (B8, "DDDD")

And a slight modification (if you want the day of the week to be capitalized).

PROPNACH (TEXT (B8; "DDDD"))

Other formulas that return the day of the week are more cumbersome, so they are not considered here, but there is one "but". The above formula will not work if the file containing it is opened in English (or any other, "non-Russian" Excel)

We took a quick look at another formula in a series of articles that we call "1 formula per day." If you are interested in the basic level of Excel, or you need "advanced", to get help, training or advice. ExcelSkype website - Excel remote learning via Skype.

The need to determine and display the week number by date arises very often. This is especially true for those people who are involved in various reporting. For example, they prepare a summary of the week's work for management.

You should remember that there are at least several approaches to week numbering.

The first approach is used by Russia. According to the standard GOST ISO 8601-2001 first working week of the year is the one that falls on the first Thursday of the year.
The second approach is that the week that falls on January 1st is considered the first week of the year.

Let us remind you once again that the first of the above-described hikes is used in Russia.

INT ((A1 -DATE (YEAR (A1 -WEEKDAY (A1 -1) +4); 1; 3) + WEEKDAY (DATE (YEAR (A1 -WEEKDAY (A1 -1) +4); 1; 3)) + 5) / 7)

When using English Excel versions the formula looks like:

INT ((A1 -DATE (YEAR (A1 -WEEKDAY (A1 -1) +4), 1,3) + WEEKDAY (DATE (YEAR (A1 -WEEKDAY (A1 -1) +4), 1,3)) + 5) / 7)

If you still need the second approach, then use the formula to determine the week number:

1 + INT ((A1 -DATE (YEAR (A1 + 4-DAY (A1 +6)); 1; 5) + DAY WEEK (DATE (YEAR (A1 + 4-DAY (A1 +6)); 1; 3) )) / 7)

formula for English version:

1 + INT ((A1 -DATE (YEAR (A1 + 4-WEEKDAY (A1 +6)), 1.5) + WEEKDAY (DATE (YEAR (A1 + 4-WEEKDAY (A1 +6)), 1.3) )) / 7)

In all the above formulas, we change A1 to a date or a reference to a date.

It's much easier if you use Excel 2007 or 2010 ... Since these versions appeared special function, allowing you to determine the number of the week by date. (note: this feature can also be enabled in Excel 2003, for this you need to connect the add-in Analysis package through the menu Service-Add-ins, but in this case, if the document is opened by a person whose setting is not enabled, he will see the #VALUE! error instead of the week number, so we will not apply it in Excel 2003)

The function is called NUMBER OF WEEKS (in english version WEEKNUM). The syntax is very simple.

WEEK NUMBERS (Date; Type)

The second parameter, Type, is very important, as the default is the second of the above approaches for starting the week number.

For Russia, you need to use the formula in the following format.

NUMBER OF WEEKS (A1; 21)

In this case, type 21 means that the number of the first week will be considered the one on which the first Thursday of the year falls and the first day of the week is Monday (note: in many countries Sunday is considered the first day of the week)

We really hope that our article helped you in solving your problem. We will be grateful if you click +1 and / or I like at the bottom of this article or share with your friends using the buttons below.

site - Endless possibilities Excel