Calculation of the date of calendar days. Razndat () function - calculating the difference of two dates in days, months, years in ms excel
To calculate the duration of time intervals, it is most convenient to use the undocumented function RAZDAT ( ) , english version DATEDIF ().
This function is not available in EXCEL2007 help and in Function wizard (SHIFT+ F3 ), but it works, although not without a flaw.
Function syntax:
DATEDATE (start_date; end_date; method_measured)
Argument start_date must come before the argument final date.
Argument measurement_method defines how and in what units the interval between the start and end dates will be measured. This argument can take the following values:
|
Value |
Description |
|
difference in full days |
|
|
difference in full months |
|
|
difference in full years |
|
|
difference in full months excluding years |
|
|
difference in days excluding months and years |
|
|
difference in days excluding years |
Below is the detailed description all 6 argument values measurement_method, as well as alternative formulas (the RAZDAT () function can be replaced with other formulas (albeit quite cumbersome). This is done in the example file).
In the example file, the argument value start_date placed in a cell A2 and the argument value final date - in a cell IN 2 .
1. The difference in full days ("d")
The formula \u003d DATEDATE (A2; B2; "d") will return the simple difference in days between two dates.
Example 1:start_date01.02.2007, final date 01.03.2009
Result1:759 (days);
Example2:start_date28.02.2007, final date 01.03.2007
Result2:1 day);
Example3:start_date28.02.2008, final date 01.03.2008
Result3:2 (days), because 2008 is a leap year;
The number of working days between two dates can be calculated using the formula \u003d NETWORKDAYS (B2; A2)
2. Difference in full months ("m")
The formula \u003d RAZDAT (A2; B2; "m") will return the number of full months between two dates.
Example 1:start_date01.02.2007, final date 01.03.2007
Result1:1 month)
Example2:start_date02.02.2007, final date 01.03.2007
Result2:0
Example3:start_date01.02.2007, final date 01.03.2009
Result3:25 months
\u003d 12 * (YEAR (B2) -YEAR (A2)) - (MONTH (A2) -MONTH (B2)) - (DAY (B2)<ДЕНЬ(A2))
Formula \u003d (YEAR (B2) -YEAR (A2)) * 12 + MONTH (B2) -MONTH (A2) the EXCEL reference gives the number of months between two dates, but not the number of full months. Those. for the dates 10/31/1961 and 11/01/1962, the formula will return 13, although in fact 12 months and 1 day have passed.
3. Difference in full years ("y")
The formula \u003d DATEDATE (A2; B2; "y") will return the number of full years between two dates.
Example 1:start_date01.02.2007, final date 01.03.2009
Result1:2 years)
Example2:start_date01.04.2007, final date 01.03.2009
Result2:1 year)
The formula can be replaced with an alternative expression:
\u003d IF (DATE (YEAR (B2), MONTH (A2), DAY (A2))<=B2;
YEAR (B2) -YEAR (A2); YEAR (B2) -YEAR (A2) -1)
4. The difference in full months excluding years ("ym")
The formula \u003d RAZDAT (A2; B2; "ym") will return the number of full months between two dates, excluding years (see examples below).
Example 1:start_date01.02.2007, final date 01.03.2009
Result1:1 (month), because the end date 01.03.2009 and the modified start date 01.02.2009 are compared. 2009
(the year of the start date is replaced by the year of the end date, since 01.02 is less than 01.03)
Example2:start_date01.04.2007, final date 01.03.2009
Result2:11 (months) because the end date 01.03.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 yearsince 01.04 is greater than 01.03)
The formula can be replaced with an alternative expression:
\u003d OSTAT (C7,12)
In a cell C7
must contain the difference in full months (see clause 2).
5. Difference in days excluding months and years ("md")
The formula \u003d 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).
Example 1:start_date01.02.2007, final date 06.03.2009
Result1:5 (days), because the end date 06.03.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_date28.02.2007, final date 28.03.2009
Result2:0 because the end date 3/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_date28.02.2009, final date 01.03.2009
Result3:4 (days) - completely incomprehensible and WRONG result. The answer should be \u003d 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!
Moreover, in EXCEL 2003 with SP3, the formula returns the correct result for 1 day. For the values \u200b\u200b12/31/2009 and 02/01/2010, the result is generally negative (-2 days)!
I do not recommend using a formula with the above argument value. The formula can be replaced with an alternative expression:
\u003d IF (DAY (A2)\u003e DAY (B2);
DAY (EONTS (EEC 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 "Once again about the RAZDAT () curvature" section below.
6. Difference in days excluding years ("yd")
The formula \u003d 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 \u003d DATEDIF (A2; B2; "yd") depends on the EXCEL version.
The formula can be replaced with an alternative expression:
\u003d IF (DATE (YEAR (B2); MONTH (A2); DAY (A2))\u003e 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 DATEDIF () function with md and ym parameters will calculate that the difference is 1 month and 14 days. Is it really?
Having a formula equivalent to RAZDAT (), you can understand the course of the calculation. Obviously, in our case, the number of full months between dates \u003d 1, i.e. all 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 is March, the previous month was February, and in 2015 there were 28 days in February). After that, it subtracts the start day and adds the day of the end date \u003d DAY (EONTS (EEC DATE (B6; -1); 0)) - DAY (A6) + DAY (B6), i.e. 28-30 + 16 \u003d 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?
Let's modify the formula for calculating the days of the difference, excluding months and years:
=IF (DAY (A18)\u003e DAY (B18); IF ((DAY (EONTHS (DATE (B18; -1); 0)) - DAY (A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))
When applying the new function, it should be borne in mind that the difference in days will be the same for several start dates (see the figure above, dates 28-31.01.2015). In other cases, the formulas are equivalent. Which formula should you use? This is for the user to decide depending on the condition of the problem.
Time calculations are constantly encountered in everyday life: from calculating days to a significant date to calculating vacation time or the period of payments on a bank loan. A collection of online calculators will help you easily deal with such a complex parameter as time.
Time
Time management is not the name of a magic spell from a computer game, but a very real ability that has gained immense popularity among businessmen and investors. Time management or is a technique for calculating time intervals for the efficient execution of a certain amount of work. Thanks to literate and rest periods, people using money management techniques manage to do much more than those who do not keep track of time and suffer from.
Naturally, time management is not only a science of time management. The most important skills to properly organize your work include:
- and manage resources;
- prioritize and;
- allocate time and analyze results.
Delegation is the handing over of work to subordinates or colleagues. Many ineffective leaders believe that no one can do better than themselves. Naturally, overwhelmed with a bunch of insignificant work, they do not have time to fulfill priority tasks, as a result of which they become ineffective.
Finding priorities is equally important. states that 80% of the result comes from 20% of the effort. In practice, this means that it is important to complete on time only those tasks on which 80% of success depends. As a rule, there are few such tasks, not strictly 20% as the Pareto principle promises, but usually in the range from 20 to 40%. It is the ability to separate the wheat from the chaff that creates productive leaders and businessmen.
The most famous, effective and at the same time the simplest technique is considered "Pomodoro". This is a time management technique, according to which work is performed at strictly allotted intervals (usually 20 minutes), each of which is accompanied by a five-minute rest. The Pomodoro technique got its name because its creator measured time intervals using a kitchen timer in the form of a tomato. Since then, modernized versions of time management have formed the basis for the success of prominent business representatives.
Timing
You can use the principles of money management not only when solving daily tasks, but also when planning large projects, the implementation of which takes weeks or months. First of all, you should find out by what time the project needs to be completed or how much time has been allocated for it. Let's take a closer look.
Number of days between two dates
This tool allows you to determine the number of days to fit between two dates. For example, on October 20, 2017, you were asked a project that must be completed by January 18, 2018. Going to the calendar and counting the time is not very convenient and it's easier to use a calculator: just select the type of program and enter both dates. In response, we see that you have 2 months and 29 days to complete the plan. Not very informative when planning. The program expresses this time also in days, weeks or months. We look. You have exactly 90 days, or 12 working weeks. With this, you can already build an effective time management system and prevent deadlines.
What date will be in n days
Another handy tool for efficient work. A large project at work can be commissioned with the note "to be completed within 50 days after the order is accepted." This is a lot of time, but again running to the calendar and calculating it is not very convenient. We use a calculator. Let's say the order was accepted for work on April 28, 2017. Until what day is it required to be handed over to the customer? Let's change the calculator type and calculate the deadline date. It will be Saturday 17 June 2017. By having the total number of days and date X handy, you can easily allocate energy to get the job done on time.
What date was n days ago
This calculator will not be useful to you in your work, but it will certainly come to the rescue in your personal life. Imagine that you received an SMS message in which your passion congratulates you on the 100th day of your life together. This is an important date that you should not forget, so it is better to use the program and find out. You received an SMS on July 4, 2017, now it is easy to find out when you moved in with your passion. So, select the type of calculator, enter the date and anniversary 100 days. Your memorable date is March 26, 2017, Sunday. It is worth circling this date on the calendar.
Time quantities
This calculator allows you to convert one time value to another. Using the program, you can express minutes into days, weeks into years, or centuries into millennia. In practice, this can come in handy when calculating working hours for freelancers and freelance artists. For example, you have 28 business days to complete your next order. It's 672 hours. Subtract 28 x 8 \u003d 224 sleep time, 28 x 4 \u003d 112 breaks and rest, and you have 336 hours of efficient work. You can already work with this and use time management techniques for productive work.
Sum / time difference
This program gives you the ability to add hours or days and calculate the total time in months, weeks, days, minutes, seconds, and even milliseconds. This is an entertaining calculator that in practice can be used to calculate the time required to complete several types of work or to calculate the free time remaining after completion of work.
If on an Excel sheet you work not only with numbers, graphs, pictures, but also with dates, then, for sure, you have come across a situation when it was necessary to calculate the difference between them. For example, you need to determine the number of days or months in a certain period of time, or calculate the age of a person so that the result is in days, months and years, or maybe you need to calculate just working days.
There is already an article about the site, and in it I touched on dates a little. But now let's take a closer look at this issue and figure out how to calculate the number of days between two dates in a simple way or using the DATEDIT () function, and how to determine the number of working days.
Method 1: subtract
Let's start with the simplest thing - we subtract the second from one date, and we get the value we need. Before doing that, make sure that the format of the cells in which the numbers are written is "Date".
If you haven't filled them in yet, then select the range you want to work with and click on the small arrow opposite the group name "Number".
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, Mar 14, 12 or another. Click OK.
In those cells for which you have just changed the format, enter the data. I have filled in 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 "\u003d" in it and press first the late (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, one. The unit is what we want to get the result in. It is substituted here:
"d" is the number of days;
"m" is the number of full months;
"y" is the number of full years;
"md" - will count days excluding months and years;
"yd" - counting days, excluding only years;
"ym" - will count the months excluding the year.
We put an equal sign in B3, write RAZDAT and open the parenthesis. Then we select the early date (A1), then the late (B1), put the appropriate unit in quotes and close the parenthesis. Put ";" between all arguments ... To calculate, press "Enter".
I got this formula:
DATEDATE (A1; B1; "d")
Choosing "d" as the unit, I got the result - 111.
If you change this value, for example, to "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 turn out to deduce the exact age of the person. One cell will contain the years "y", the second months "ym", the third days "md"
Method 3: count working days
Let's take such a sign for example. In column A we have the beginning of the month or the starting date of the countdown, in B - the end of the month or countdown. This function counts working days without taking into account Saturdays and Sundays, but the months also have holidays, so we fill in the C column with the corresponding dates.
CLEANING DAYS (A5; B5; C5)
As arguments, we indicate the start date (A5), then the end date (B5). The last argument is the holidays (C5). 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 cell (D6) and put equal in it. Then press the letter "f" in the formula bar. A window will open "Insert function"... In the Category field, select "Complete alphabetical listing" and find the function you want in the list. Click OK.
Next, you need to select the function arguments. In "Start_date" we select the initial value (A6), in "End_date" - the final one (B6). In the last field, enter the dates of the holidays in brackets () and quotation marks "". Then click "OK".
As a result, we get the following function and the value will be calculated without taking into account weekends and the specified holidays:
CLEANINGDAYS (A6; B6; ("01.01.17"; "07.01.17"))
In order not to register holidays manually, you can specify a specific range in the corresponding field. I have it C6: C7.
Working days will be counted, and the function will look like:
CLEANING DAYS (A6; B6; C6: C7)
Now let's make a calculation for the last month. We introduce the function and fill in its arguments:
CLEANING DAYS (A8; B8; C8)
In February, it turned out to be 19 working days.
About others, I wrote a separate article, and you can read it by clicking on the link.
Choose one of the described methods and calculate the difference in Excel between the required dates in days, months or years, but if necessary, then calculate only the number of working days.