Computing the difference between two dates. Calculating Date Differences in Microsoft Excel

If on an Excel sheet you work not only with numbers, graphs, pictures, but also with dates, then you probably came across a situation where you had to calculate the difference between them. For example, you need to determine the number of days or months for a certain period of time, or calculate the age of a person so that the result is in days, months and years, or maybe it is required to calculate working days.

There is already an article about calculating the difference in Excel on the site, and in it I touched on dates a little. But now let's look at this issue in more detail and figure out how to calculate the number of days between two dates. in a simple way or using the RAZDAT () function, and how to determine the number of working days.

Method 1: subtraction

Let's start with the simplest - we subtract the second from one date, and we get the value we need. Before that, make sure that the format of the cells in which the numbers are entered is selected "Date".

If you haven’t filled them in yet, then select the range you want to work with and click on the small arrow next to the name of the “Number” group.

In the window that opens on the left, select the format that suits us, and then in the main area decide on the type: 03/14/12, 14 Mar 12 or another. Press "OK".

In those cells for which you have just changed the format, enter data. I filled out A1 and B1. Now you need to select any cell ( D1 ) in which the general data format is set, otherwise the calculations will be incorrect. Put “=” in it and press first the latest (B1) date, then the early (A1). To calculate the number of days between them, press "Enter".

Method 2: using a function

To do this, select the cell in which the result will be (B3), and see that the general format is selected for it.

To calculate the days, we will use the RAZDAT() function. It includes three arguments: start and end date, unit. The unit is what we want to get the result in. Substituted here:

"d" - the number of days;
"m" - the number of full months;
"y" - the number of full years;
"md" - will count the days without taking into account months and years;
"yd" - counting days without taking into account only years;
"ym" - counts the months without taking into account the year.

We put an equal sign in B3, write RAZDAT and open the bracket. Then we select the early date (A1), then the late one (B1), put the appropriate unit in quotation marks and close the bracket. Between all arguments put ";" . To calculate, press "Enter".

I got this formula:

RAZNDAT(A1;B1;"d")

Choosing “d” as the unit, I got the result - 111.

If change given value, for example, on "md", then the formula will calculate the difference between 5 and 24, excluding months and years.

By changing this argument in this way, it will be possible to derive the exact age of a person. In one cell there will be years "y", the second months "ym", the third days "md"

Method 3: counting working days

Let's take this table as an example. In column A we have the beginning of the month or the starting date of the count, in B we have the end of the month or the count. This function counts working days, excluding Saturdays and Sundays, but there are also holidays in months, so column C will be filled in with the corresponding dates.

NETWORKDAYS(A5;B5;C5)

As arguments, we specify the start date (A5), then the end date (B5). The last argument is the holidays (C5). We separate them ";" .

By pressing "Enter" the result will appear, in the example cell D5 - 21 days.

Now consider if there are several holidays in a month. For example, in January New Year and Christmas. Select the cell ( D6 ) and put it equal. Then click on the letter "f" in the formula bar. A window will open "Insert Function". In the "Category" field, select "Full alphabetical list" and find in the list desired function. Click OK.

Next, you need to select the arguments of the function. In "Start_date" we select the initial value (A6), in "End_date" - the final (B6). In the last field, enter the dates of the holidays in brackets () and quotes "". Then press "OK".

As a result, we will get the following function and the value will be calculated excluding weekends and specified holidays:

NETWORKDAYS(A6,B6,("01/01/17", "01/07/17"))

In order not to register holidays manually, you can specify a certain range in the corresponding field. I have it C6:C7.

Working days will be counted, and the function will look like:

NETWORKDAYS(A6;B6;C6:C7)

Now let's calculate for last month. Enter the function and fill in its arguments:

NETWORKDAYS(A8;B8;C8)

In February, it turned out 19 working days.

About other date and time functions in Excel, I wrote a separate article, and you can read it by clicking on the link.

To calculate the duration of time intervals, it is most convenient to use the undocumented function RAZDAT ( ) , English version DATEDIF().

The RAZDAT() function is not in the EXCEL2007 help and in Function Wizard (SHIFT+ F3 ), but it works, although not without a flaw.

Function syntax:

RAZDAT(start_date; end_date; measure_method)

Argument start_date must be before the argument final date.

Argument method_of_measurement determines how and in what units the interval between the start and end dates will be measured. This argument can take the following values:

Below is detailed description all 6 argument values method_of_measurement, as well as alternative formulas (the RAZDAT () function can be replaced with other formulas (albeit rather cumbersome). This is done in the example file).

In the example file, the value of the argument start_date placed in a cell A2 , and the value of the argument final date- in a cell IN 2 .

1. Difference in days ("d")

The formula =RADIDAT(A2;B2;"d") will return the simple difference in days between two dates.

Example1:start_date 25.02.2007, final date 26.02.2007
Result: 1 day).

This example shows that when calculating seniority it is necessary to use the RAZDAT() function with care. Obviously, if the employee worked on February 25 and 26, then he worked 2 days, not 1. The same applies to the calculation of full months (see below).

Example2:start_date 01.02.2007, final date 01.03.2007
Result: 28 (days)

Example3:start_date 28.02.2008, final date 01.03.2008
Result: 2 (days), because 2008 is a leap year

Note: If you are only interested in working days, then k between two dates can be calculated using the formula = NETWORKDAYS (B2, A2)

2. Difference in full months ("m")

The formula =RAZDAT(A2;B2;"m") will return the number of full months between two dates.

Example1:start_date 01.02.2007, final date 01.03.2007
Result: 1 month)

Example2:start_date 01.03.2007, final date 31.03.2007
Result: 0

When calculating the length of service, it is considered that an employee who has worked all the days of the month has worked 1 full month. The RAZDAT() function doesn't think so!

Example3:start_date 01.02.2007, final date 01.03.2009
Result: 25 months

=12*(YEAR(B2)-YEAR(A2))-(MONTH(A2)-MONTH(B2))-(DAY(B2)<ДЕНЬ(A2))

Attention: In MS EXCEL help (see section Calculating Age) there is a curved formula for calculating the number of months between 2 dates:

=(YEAR(TODATE())-YEAR(A3))*12+MONTH(TODATE())-MONTH(A3)

If instead of the function TDATE () - the current date, use the date 10/31/1961, and enter 01/11/1962 in A3, then the formula will return 13, although in fact 12 months and 1 day have passed (November and December in 1961 + 10 months in 1962) .

3. Difference in full years ("y")

The formula =RAZDAT(A2;B2;"y") will return the number of complete years between two dates.

Example1:start_date 01.02.2007, final date 01.03.2009
Result: 2 years)

Example2:start_date 01.04.2007, final date 01.03.2009
Result: 1 year)

The formula can be replaced with an alternative expression:
=IF(DATE(YEAR(B2),MONTH(A2),DAY(A2))<=B2;
YEAR(B2)-YEAR(A2);YEAR(B2)-YEAR(A2)-1)

4. Difference in full months excluding years ("ym")

The formula =RAZDAT(A2;B2;"ym") will return the number of full months between two dates, excluding years (see examples below).

Example1:start_date 01.02.2007, final date 01.03.2009
Result: 1 (month), because the end date 01.03.2009 and the modified start date 01.02 are compared. 2009 (the year of the start date is replaced by the year of the end date, because 01.02 is less than 01.03)

Example2:start_date 01.04.2007, final date 01.03.2009
Result: 11 (months), as the end date 03/01/2009 and the modified start date 01/04 are compared. 2008 (the year of the start date is replaced by the year of the end date minus 1 year, because 01.04 more than 01.03)

The formula can be replaced with an alternative expression:
=MOD(C7,12)
In a cell C7 must contain the difference in full months (see point 2).

5. Difference in days excluding months and years ("md")

The formula =RAZDAT(A2;B2;"md") will return the number of days between two dates, excluding months and years. It is not recommended to use the RAZDAT() function with this argument (see examples below).

Example1:start_date 01.02.2007, final date 06.03.2009
Result1: 5 (days), because the end date 03/06/2009 and the modified start date 01 are compared. 03 .2009 (the year and month of the start date is replaced by the year and month of the end date, since 01 is less than 06)

Example2:start_date 28.02.2007, final date 28.03.2009
Result2: 0, because the end date 03/28/2009 and the modified start date 28 are compared. 03 .2009 (the year and month of the start date is replaced by the year and month of the end date)

Example3:start_date 28.02.2009, final date 01.03.2009
Result3: 4 (days) - a completely incomprehensible and WRONG result. The answer should be =1. Moreover, the calculation result depends on the EXCEL version.

EXCEL 2007 version with SP3:

The result is 143 days! More than days in a month!

EXCEL 2007 version:

The difference between 02/28/2009 and 03/01/2009 is 4 days!

And in EXCEL 2003 with SP3 the formula returns the correct result for 1 day. For the values ​​12/31/2009 and 02/01/2010 the result is generally negative (-2 days)!

I do not advise using the formula with the above argument value. The formula can be replaced with an alternative expression:
=IF(DAY(A2)>DAY(B2);
DAY(EOMONTH(DATE(B2;-1);0))-DAY(A2)+DAY(B2);
DAY(B2)-DAY(A2))

This formula is only an equivalent (in most cases) expression for RAZDAT() with the md parameter. Read about the correctness of this formula in the section "Once again about the curvature of RAZDAT ()" below.

6. Difference in days excluding years ("yd")

The formula =RAZDAT(A2;B2;"yd") will return the number of days between two dates, excluding years. It is not recommended to use it for the reasons stated in the previous paragraph.

The result returned by the formula =RAZDAT(A2;B2;"yd") depends on the version of EXCEL.

The formula can be replaced with an alternative expression:
=IF(DATE(YEAR(B2),MONTH(A2),DAY(A2))>B2;
B2-DATE(YEAR(B2)-1,MONTH(A2),DAY(A2));
B2-DATE(YEAR(B2),MONTH(A2),DAY(A2)))

Once again about the curvature RAZDAT ()

Let's find the difference between the dates 03/16/2015 and 01/30/15. The RAZDAT() function with the md and ym parameters will calculate that the difference is 1 month and 14 days. Is it really?

Having a formula equivalent to RAZDAT() , one can understand the calculation process. Obviously, in our case, the number of full months between dates = 1, i.e. the whole of February. To calculate days, the function finds the number of days in the previous month relative to the end date, i.e. 28 (the end date belongs to March, the previous month is February, and in 2015 February had 28 days). After that subtracts the day of the start and adds the day of the end date = DAY(EOMONTH(DATE(B6,-1),0))-DAY(A6)+DAY(B6), i.e. 28-30+16=14. In our opinion, there is still 1 full month between the dates and all the days of March, i.e. 16 days, not 14! This error occurs when there are fewer days in the previous month relative to the end date than the days of the start date. How to get out of this situation?

We modify the formula for calculating days of difference without taking into account months and years:

=IF(DAY(A18)>DAY(B18),IF((DAY(EOMONTH(DATE(B18,-1),0))-DAY(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

When applying the new function, it must be taken into account that the difference in days will be the same for several starting dates (see the figure above, the dates 28-31.01.2015). In other cases, the formulas are equivalent. What formula to apply? It is up to the user to decide, depending on the condition of the problem.

To perform certain tasks in Excel, you need to determine how many days have passed between certain dates. Fortunately, the program has tools that can solve this issue. Let's find out how you can calculate the date difference in Excel.

Before you start working with dates, you need to format the cells for this format. In most cases, when you enter a character set that looks like a date, the cell will reformat itself. But it is still better to do it manually to insure yourself against surprises.

Now all the data that will be contained in the selected cells will be recognized by the program as a date.

Method 1: simple calculation

The easiest way to calculate the difference of days between dates is with a simple formula.

Method 2: RAZDAT function

You can also use a special function to calculate the difference in dates RAZDAT. The problem is that it is not in the list of the Function Wizard, so you have to enter the formula manually. Its syntax looks like this:

RAZDAT(start_date, end_date, unit)

"Unit"- this is the format in which the result will be displayed in the selected cell. The symbol used in this parameter determines the units in which the total will be returned:

• "y" - full years;
• "m" - complete months;
• "d" - days;
• "YM" - difference in months;
• "MD" - the difference in days (months and years are not taken into account);
• "YD" - the difference in days (years are not taken into account).

You also need to note that, unlike the simple formula method described above, when using this function, the start date must be in the first place, and the end date should be in the second. Otherwise, the calculations will be incorrect.

Method 3: calculating the number of working days

Excel also has the ability to calculate the working days between two dates, that is, excluding weekends and holidays. For this, the function is used CHISTRABNI. Unlike the previous statement, it is listed in the Function Wizard. The syntax for this function is:

NETWORKDAYS(start_date, end_date, [holidays])

In this function, the main arguments are the same as those of the operator RAZDAT- start and end date. In addition, there is an optional argument "Holidays".

It should be replaced by the dates of public holidays, if any, for the period covered. The function calculates all days of the specified range, excluding Saturdays, Sundays, and also those days that are added by the user to the argument "Holidays".

After the above manipulations, the number of working days for the specified period will be displayed in the pre-selected cell.

As you can see, the Excel program provides its users with a fairly convenient tool for calculating the number of days between two dates. At the same time, if you just need to calculate the difference in days, then the best option would be to use a simple subtraction formula, rather than using the function RAZDAT. But if you need, for example, to calculate the number of working days, then the function will come to the rescue NETWORKDAYS. That is, as always, the user should decide on the execution tool after he has set a specific task.

A calendar is a way of counting and displaying large periods of time.
All our calculations are carried out within the framework of the Gregorian calendar now generally accepted throughout the world (the average length of a year is 365.2425 days). When making historical calculations, please check the dates. The difference between the indications of the Gregorian and Julian calendar is now 13 days, but in the early centuries the number of days between equal dates was smaller, the smaller, the closer to the beginning of our era ().

Date 1

sun 22 . 06 . 1941 04 : 00

original date

21

Interval

1417

days between dates

0

centuries between dates

Date 2

Tue 09 . 05 . 1945 01 : 43

final date

21

1012

working days between dates *

3

years between dates

46

months between dates

34009

hours between dates

2040583

minutes between dates

122434980

seconds between dates

3

10

17

1

43

years months days hours minutes
between two dates

The number of minutes and seconds in the date cannot exceed 60, you entered ... other date parameters will be changed
The number of hours in the date cannot exceed 23, you entered: ... - other date parameters will also be changed
Attention!
Holidays in Russia are taken into account in full only if both dates refer to 2018
The number of days in a month \n cannot be more than 31

Oh-oh!
The number you entered refers to times that are hard to imagine...

Sorry!

Here is a simple online calculator that is aware of its, alas, modest capabilities, and not an astronomical program!

Please enter another number.

Based on this small scoreboard created.

Now the calculation table is configured to count the days until the beginning of summer.

To calculate how many days have passed or will pass between the dates you are interested in, simply enter them in the appropriate fields of the table. The time interval can be changed in the same way as dates, while the countdown will be from "Date 1", and "Date 2" will change.
The calculation results also display unchanging information values ​​​​and indicators - these are the days of the week (dark gray - weekdays, orange-red - weekends) and, as a final summary, the interval between dates, expressed in years, months, days, hours and minutes .

If on the scoreboard you see the most fateful period of history for our country - the days between the dates of the Great Patriotic War, then it means that Java Script is disabled in your browser and must be enabled for calculations.

* Within 2019 of the year working days calculation ongoing taking into account the holidays of Russia and approved by the government of the Russian Federation scheme for the transfer of days off. For long periods of time between dates, the calculation of the number of working days is based on the assumption of a five-day working week, holidays are not taken into account.

Sergey Ov(seosnews9)

Reference: It is authentically known that at the end of the first millennium in Ancient Russia, the time was counted according to the Julian calendar, although the new year was celebrated on March 1, the countdown according to such a calendar is called the March style. The difference between equal dates of the modern and ancient calendar on March 1, 1000 was 59 + 6 = 65 days (6 days difference between the readings of the Julian and the Gregorian calendar, equal dates are considered dates with equal numbers and equal numbers of months from the beginning of the year).
In 1492, by a resolution of the Moscow Cathedral of the Russian Orthodox Church, a calendar was adopted, according to which the new year (New Year) began on September 1 ( September style ), the difference with the modern calendar was 9-122=-113 days.
Two centuries later, on the eve of the round calendar date, Peter the Great introduces a calendar leading from the Nativity of Christ. The New Year in Russia is celebrated on January 1 from the beginning of 1700 (although, in fact, according to the modern calendar, this new year came on January 11, 1700). The country moved from 7208 to 1700! So, in the then new 18th century, which came in 1701, Russia entered almost in step with Europe. Almost in step, because the chronology, as before, was conducted according to the Julian calendar (only the date has changed, now it is called old style) , while Europe has already partially switched to the Gregorian calendar.
The modern Gregorian calendar in Russia was adopted only in the 21st century. January 26, 1918: Vladimir Ilyich Lenin signed a decree of the Council of People's Commissars on the transition to a new time calculation, while the dating shifted by 13 days. The last state document dated according to the old style was issued on January 31, 1918 - the next day was February 14!
So the question is, "How many days are there between two dates?" in a historical sense, always requires clarification ...