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 by 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.

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 specify 0 as the day value. In other words, the "0th" 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 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.

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 a numeric value that characterizes 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. A cell from Excel tables, 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 unsupported data was passed to the input of the DAY function Excel type, 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 option has the features described in the notes for the previous feature.
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 output the text value corresponding to 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 written in the format Excel dates, in which employees of a certain enterprise must 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. Software reads data from an Excel spreadsheet 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.

When working in Excel program Sometimes the task is set so that after entering a specific date in a cell, the day of the week that corresponds to it is displayed. Naturally, decide this task through such a powerful table processor, like Excel, perhaps, and in several ways. Let's see what options exist to perform this operation.

There are several ways to display the day of the week based on an entered date, ranging from formatting cells to using 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 particular situation.

Method 1: Apply Formatting

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

In field "Type" formatting windows instead of value "DDDD" you can also enter the expression:

In this case, the sheet will display the abbreviated name of the day of the week.

Method 2: Using the TEXT function

But the method that was presented above involves converting the date to the day of the week. Is there an option to have both of these values ​​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, such an option exists. This can be done using the formula TEXT. In this case, the value we need will be output to 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 what day of the week it will fall on.

Method 3: Using the WEEKDAY function

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

As with the previous function, when you change the date, the number of the day of the week in the cell in which the operator is installed automatically changes.

As you can see, in Excel there are three main options for representing a 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 of them 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 which of these options is most suitable for him in a particular situation.

In this lesson, you will learn various useful formulas for adding and subtracting dates in Excel. For example, you will learn how to subtract another from one date, how to add several days, months or years to a date, etc.

If you have already taken lessons on working with dates in Excel (ours or any other lessons), then you should know the formulas for calculating units of time, such as days, weeks, months, years.

When analyzing dates in any data, you often need to perform arithmetic operations on these dates. This article will explain some formulas for adding and subtracting dates that you may find useful.

How to subtract dates in Excel

Let's assume that in your cells A2 And B2 contains dates, and you need to subtract one date from another to find out how many days there are between them. As is often the case in Excel, this result can be obtained in several ways.

Example 1. Directly subtract one date from another

I think you know that Excel stores dates as integers starting at 1, which corresponds to January 1, 1900. So you can simply arithmetically subtract one number from another:

Example 2: Subtracting dates using the DATEDAT function

If the previous formula seems too simple to you, the same result can be obtained in a more sophisticated way using the function RAZNDAT(DATEDIF).

RAZNDAT(A2;B2,"d")
=DATEDIF(A2,B2,"d")

The following figure shows that both formulas return the same result, except for row 4, where the function RAZNDAT(DATEDIF) returns an error #NUMBER!(#NUM!). Let's see why this happens.

When you subtract a later date (May 6, 2015) from an earlier date (May 1, 2015), the subtraction operation returns a negative number. However, the function syntax RAZNDAT(DATEDIF) does not allow start date was more end date and, of course, returns an error.

Example 3. Subtract the date from the current date

To subtract a specific date from the current date, you can use any of the previously described formulas. Just use the function instead of today's date TODAY(TODAY):

TODAY()-A2
=TODAY()-A2

RAZNDAT(A2;TODAY();"d")
=DATEDIF(A2,TODAY(),"d")

As in the previous example, the formulas work fine when the current date is greater than the subtracted date. Otherwise the function RAZNDAT(DATEDIF) returns an error.

Example 4: Subtracting Dates Using the DATE Function

If you prefer to enter dates directly into the formula, specify them using the function DATE(DATE) and then subtract one date from the other.

Function DATE has the following syntax: DATE OF( year; month; day) .

For example, the following formula subtracts May 15, 2015 from May 20, 2015 and returns the difference - 5 days.

DATE(2015,5,20)-DATE(2015,5,15)
=DATE(2015,5,20)-DATE(2015,5,15)

If needed count the number of months or years between two dates, then the function RAZNDAT(DATEDIF) – the only one Possible Solution. In the continuation of the article you will find several examples of formulas that reveal this function in detail.

Now that you know how to subtract one date from another, let's see how you can add or subtract a certain number of days, months or years from a date. There are several for this Excel functions. Which one to choose depends on what units of time need to be added or subtracted.

How to add (subtract) days to a date in Excel

If you have a date in a cell or a list of dates in a column, you can add (or subtract) a certain number of days to them using the appropriate arithmetic operation.

Example 1: Adding days to a date in Excel

The general formula for adding a certain number of days to a date is:

= date + N days

The date can be set in several ways:

• Cell reference:
• Calling a function DATE(DATE):

  DATE(2015;5;6)+10
  =DATE(2015,5,6)+10

• Calling another function. For example, to add several days to the current date, use the function TODAY(TODAY):

  TODAY()+10
  =TODAY()+10

The following figure shows the operation of these formulas. At the time of writing current date was May 6, 2015

Note: The result of these formulas is an integer representing a date. To show it as a date, you need to select the cell (or cells) and click Ctrl+1. A dialog box will open Cell Format(Format Cells). On the tab Number(Number) in the list number formats select date(Date) and then specify the format you need. More detailed description You will find it in the article.

Example 2: Subtracting days from a date in Excel

To subtract a certain number of days from a date, you again need to use the normal arithmetic operation. The only difference from the previous example is minus instead of plus

= date - N days

Here are some examples of formulas:

A2-10
=DATE(2015,5,6)-10
=TODAY()-10

How to add (subtract) several weeks to a date

When you need to add (subtract) several weeks to a certain date, you can use the same formulas as before. You just need to multiply the number of weeks by 7:

• Add N weeks to date in Excel:

  A2+ N weeks * 7

  For example, to add 3 weeks to a date in a cell A2, use the following formula:

• Subtract N weeks from date in Excel:

  A2 - N weeks * 7

  To subtract 2 weeks from today's date, use this formula:

  TODAY()-2*7
  =TODAY()-2*7

How to add (subtract) several months to a date in Excel

To add (or subtract) a certain number of months to a date, you need to use the function DATE(DATE) or DATAMES(EDATE) as shown below.

Example 1: Adding several months to a date using the DATE function

If the list of dates is, for example, in the column A, indicate the number of months that you want to add (positive number) or subtract (negative number) in some cell, say, in C2.

Type in cell B2 formula below, click on the highlighted corner of the cell and drag it down the column with the mouse B to the last filled cell in the column A. Formula from cell B2 will be copied to all cells of the column B.

DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))

Let's see what this formula does. The logic of the formula is clear and obvious. Function DATE OF( year; month; day) receives the following arguments:

• Year from date in cell A2;
• Month from date in cell A2+ number of months indicated in the cell C2;
• Day from date in cell A2;

It's simple! If you enter in C2 negative number, the formula will subtract months rather than add.

Naturally, nothing prevents you from entering a minus directly in the formula to subtract months:

DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))

And, of course, you can specify the number of months to add or subtract directly in the formula without cell reference. The finished formulas will look something like this:

• Add months to date:

  DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))
  =DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))

• Subtract months from date:

  DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))
  =DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))

Example 2: Adding or subtracting months from a date using the DATAMES function

Excel provides special function, which returns a date that is a certain number of months ago or forward from a given date, is a function DATAMES(EDATE). It is available in latest versions Excel 2007, 2010, 2013 and new Excel 2016.

Using DATAMES(EDATE) You provide the following two arguments:

• Start date – the date from which the number of months is counted.
• Months – the number of months to be added (positive number) or subtracted (negative number).

These formulas will give the same result as formulas with the function DATE(DATE) in the previous example:

When using the function DATAMES(EDATE) the start date and number of months can be specified directly in the formula. Dates can be set using the function DATE(DATE) or as a result of executing other formulas. For example:

• This formula adds 10 months to May 7, 2015

  DATAMES(DATE(2015,5,7),10)
  =EDATE(DATE(2015,5,7),10)

• This formula subtracts 10 months from today's date

  DATAMES(TODAY();-10)
  =EDATE(TODAY(),-10)

Note: Function DATAMES(EDATE) returns just an integer. To represent it as a date, you need to apply a date format to the cell. How to do this is indicated in the article How to change date format in Excel.

How to add (subtract) years to a date in Excel

Adding years to dates in Excel is the same as adding months. You need to use the function again DATE(DATE), but this time you need to specify the number of years you want to add:

DATE(YEAR( date) + N years; MONTH( date); DAY( date))
= DATE(YEAR( date) + N years,MONTH( date), DAY( date))

On Excel sheet, the formulas might look like this:

• Add 5 years to the date specified in the cell A2:

  DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
  =DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))

• Subtract 5 years from the date specified in the cell A2:

  DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))
  =DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))

To get a universal formula, you can enter the number of years in a cell, and then refer to that cell in the formula. A positive number will add years to the date, and a negative number will subtract.

Adding (subtracting) days, months and years to a date

If you carefully read the two previous examples, then I think you figured out how to add (or subtract) years, months and days to a date in one formula. Yes, using the good old function DATE(DATA)!

• For additions X years, Y months and Z days:

  DATE(YEAR( date) + X years; MONTH( date) + Y months; DAY( date) + Z days)
  = DATE(YEAR( date) + X years,MONTH( date) + Y months, DAY( date) + Z days)

• For subtraction X years, Y months and Z days:

  DATE(YEAR( date) - X years; MONTH( date) - Y months; DAY( date) - Z days)
  = DATE(YEAR( date) - X years,MONTH( date) - Y months, DAY( date) - Z days)

For example, the following formula adds 2 years and 3 months, and subtracts 15 days from the date in the cell A2:

DATE(YEAR(A2)+2;MONTH(A2)+3;DAY(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)

When applied to our date column, the formula takes the following form:

DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)

How to Add and Subtract Time in Excel

IN Microsoft Excel You can add and subtract time using the function TIME(TIME). It allows you to handle units of time (hours, minutes and seconds) in the same way as years, months and days in the function DATE(DATE).

• Add time in Excel:

  A2 + TIME( watch; minutes; seconds)
  = A2 + TIME( watch, minutes, seconds)

• Subtract time in Excel:

  A2 - TIME( watch; minutes; seconds)
  = A2 - TIME( watch, minutes, seconds)

  Where A2– this is a cell with the time that needs to be changed.

For example, to add 2 hours 30 minutes and 15 seconds to the time in a cell A2 you need to use the following formula:

A2+TIME(2;30;15)
=A2+TIME(2,30,15)

A2+TIME(2;30;-15)
=A2+TIME(2.30,-15)

You can also enter required values into worksheet cells and refer to them in the formula:

A2+TIME($C$2,$D$2,$E$2)
=A2+TIME($C$2,$D$2,$E$2)