MS Excel has an extremely interesting feature that few people know about. So little that Excel does not even provide a contextual hint for this function when entering, although, oddly enough, it is in the program help and is described quite well. It's called RAZNDAT() or DATEDIF() and serves to automatically calculate the difference in days, months or years between two given dates.
Doesn't sound like much? In fact, sometimes the ability to quickly and accurately calculate how much time has passed since an event can be very useful. How many months have passed since your birthday, how long have you been sitting at this job, or how many days have you been on a diet - but who knows how many uses this useful function has? And most importantly, the calculation can be automated and every time you open an MS Excel workbook, you can receive accurate data specifically for today! Sounds interesting, doesn't it?
The RAZNDAT() function takes three arguments:
- Start date- date from which the account is kept
- Final date- to which the count is made
- Unit- days, months, years.
It is written like this:
=DATE(start date, end date, unit of measure)
Units of measurement are written as:
- "y"— date difference in full years
- "m"— date difference in full months
- "d"— date difference in full days
- "yd"— date difference in days from the beginning of the year, excluding years
- "md"— date difference in days excluding months and years
- "ym"— date difference in full months excluding years
In other words, to calculate my current age in years, I write the function as:
=RASDAT(07/14/1984;03/22/2016;"y")
Please note that the last argument is always enclosed in quotes.
If I want to get the exact age, then I will write a complicated formula:
=RAZNDAT(F2;G2;"y")&" year "&RAZNDAT(F2;G2;"ym")&" months"
In which the RAZNDAT() function is called twice at once, with different meanings, and the words “year” and “months” are simply joined to the result. That is, the real power of the function only appears when it is combined with other MS Excel features.
Another interesting option is to add a counter to the function that moves daily relative to today’s date. For example, if I decide to write a formula that calculates the number of days until my vacation in standard form, it will look something like this:
And everything would be correct if, opening this sheet a week later, I would see that the number of days until vacation has decreased. However, I will see the same number - because the original dates have not changed. Accordingly, I would have to change the current date, and then the RAZNDAT() function would do everything correctly.
To avoid this annoying little thing, as the first argument (today's date), I will substitute not a reference to the value stored in the cell, but another function. This function is called TODAY() and its main and only task is to return today's date.
Once, and the problem is solved - from now on, whenever I open this MS Excel sheet, the RAZNDAT() function will always show me the exact value calculated taking into account today's date.
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 to fit this format. In most cases, when you enter a set of characters similar to a date, the cell itself is reformatted. But it’s better to do it manually to protect yourself from surprises.
Now the program will recognize all the data that will be contained in the selected cells as a date.
Method 1: simple calculation
The easiest way to calculate the difference in days between dates is to use the usual formula.
Method 2: RAZNDAT function
You can also use a special function to calculate the difference in dates RAZNDAT. The problem is that it is not in the Function Wizard list, so you will have to enter the formula manually. Its syntax looks like this:
RAZNDAT(start_date, end_date, unit)
"Unit"— this is the format in which the result will be displayed in the selected cell. The units in which the total will be returned depends on which character is inserted into this parameter:
- "y" - full years;
- "m" - full months;
- "d" - days;
- "YM" - difference in months;
- “MD” is the difference in days (months and years are not taken into account);
- “YD” is the difference in days (years are not taken into account).
You should also note that, unlike the simple formula method described above, when using this function, the start date should be in the first place, and the end date in the second. Otherwise, the calculations will be incorrect.
Method 3: calculating the number of working days
In Excel it is also possible to calculate working days between two dates, that is, excluding weekends and holidays. To do this, use the function CHISTRABNI. Unlike the previous operator, it is present in the Function Wizard list. The syntax for this function is as follows:
NETWORKDAYS(start_date, end_date, [holidays])
In this function the main arguments are the same as those of the operator RAZNDAT– start and end date. There is also an optional argument "Holidays".
Instead, you should substitute the dates of non-working holidays, if any, for the period covered. The function calculates all days of the specified range, excluding Saturdays, Sundays, as well as those days 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, Excel provides its users with quite convenient tools 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 RAZNDAT. But if you need, for example, to count 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.
Use the DATEDIF function when you need to calculate the difference between two dates. First, enter a start date in a cell and an end date in another. Then enter a formula, such as one of the following.
Day difference
In this example, the Start Date is in cell D9 and the End Date is in E9. The formula will be shown on F9. "D" returns a number full days between two dates.
Week difference
In this example, the Start Date is in cell D13 and the End Date is in E13. "D" returns the number of days. But notice what's at the end /7 . This divides the number of days by 7, since there are 7 days in a week. Note that this result also needs to be formatted as a number. Press CTRL + 1. Then click number _gt_ decimal places: 2.
Difference in months
In this example, the Start Date is in cell D5 and the End Date is in the down cell. In the formula "m" returns a number full months between two days.
Year difference
In this example, the Start Date is in cell D2 and the End Date is in E2. "Y"
Calculating age in accumulated years, months and days
1. Use RAZNDAT to find the total number of years.
In this example, the Start Date is in cell D17 and the End Date is in E17. In the formula "y" returns the number of complete years between two days.
2. To search for months, use RAZNDAT again, indicating "GM".
In another cell, use the RAZNDAT formula with the parameter "GM". "GM" returns the number of months remaining after the last full year.
3. Use a different formula to find days.
Now we need to find the number of remaining days. This can be done by writing a different type of formula shown above. This formula subtracts the first day of the month end (05/01/2016) from the original end date in cell E17 (05/06/2016). Here's how it's done: First, the DATE function creates the date 05/01/2016. It is created using the year in cell E17 and the month in cell E17. 1 denotes the first day of the month. The result of the DATE function will be 05/01/2016. We then subtract this date from the original end date in cell E17 (05/06/2016), resulting in 5 days.
4. optional: Combine three formulas into one.
You can place all three calculations in one cell, as shown in this example. Use of ampersands, quotation marks and text. This is a longer formula to enter, but at least it's all in one of them. Advice. Press Alt+Enter to place line breaks in the formula. This makes it easier to read. Also, if you can't see the entire formula, press CTRL+SHIFT+U.
Downloading examples
You can download a sample book with all the examples given in this article. You can subscribe to them or create your own formulas.
Other date and time calculations
Calculation between today and another date
As shown above, the DATEDIF function calculates the difference between the start date and the end date. However, instead of entering specific dates, you can also use the function Today() in the formula. When you use the TODAY() function, Excel uses the current date on your computer. Keep in mind that if you open the file again in the future, the file will change.
Calculation of working days with or without holidays
Use NETWORKDAYS. INTL if you want to calculate the number of business days between two dates. Additionally, you can also exclude weekends and holidays.
Before you begin, follow these steps: Decide whether to exclude holiday dates. If so, enter the list of holiday dates in a separate area or sheet. Each holiday day is placed in a separate cell. Then select those cells and then select formulas _gt_ assign Name. Name the range micholidais and press the button OK. Then create a formula using the steps below.
1. Enter the start date and end date.
In this example, the Start Date is in cell D53 and the End Date is in cell E53.
2. In another cell, enter a formula, for example:
Enter a formula, such as the example above. 1 in the formula defines "Saturday" and "Sunday" as days off and excludes them from the total.
Note. Excel 2007 does not have NETWORKDAYS. INTERNATIONAL However, he has CLEAR DAYS. The above example would look like this in Excel 2007: = NETWORKDAYS (D53, E53). You do not specify 1 because NETWORKDAYS assumes that the weekends are Saturday and Sunday.
3. Change value 1 if necessary.
If Saturday and Sunday are not holidays, change the value 1 to something else in the IntelliSense list. For example, 2 sets Sunday and Monday as weekends.
If you are using Excel 2007, skip this step. The NETWORKDAYS function in Excel 2007 always assumes that weekends are Saturday and Sunday.
4. Enter a name for the holiday range.
If you created a holiday range name in the Getting Started section above, enter it at the end as shown below. If you don't have holidays, you can leave comma and Micholydays. If you are using Excel 2007, the above example would look like this: = NETWORKDAYS (D53, E53, micholidays).
Feather If you don't want to reference the holiday range name, you can also enter a range like D35:E:39. Alternatively, you can enter each holiday in the formula. For example, if the holidays are January 1st and 2nd of 2016, enter them as follows: = NETWORKDAYS. Int (D53, E53, 1, ("1/1/2016", "1/2/2016")). In Excel 2007 it will look like this: = NETWORKDAYS (D53, E53, ("1/1/2016", "1/2 . 2016"})
Calculation of time spent
To calculate the time spent, you can subtract one time from the other. First, enter the start time in a cell and the end time in another. Make sure that all times, including hours, minutes and spaces, are filled in before noon or PM. Here's what you need to do to do this:
1. Enter the start time and end time.
In this example, the start time is in cell D80 and the end time is in E80. Make sure you enter hours, minutes and spaces before the characters AM and PM.
2. Set the h/pm format.
Select both dates and press Ctrl+1 (or +1 on a Mac). Make sure the option is selected custom _gt_ h/pm if it is not already installed.
3. subtracting two values.
In another cell, subtract the start cell from the "end time" cell.
4. Set the hour format.
Press CTRL+1 (or +1 on Mac). Select " custom _gt_" to exclude the results of "AM" and "PM".
A task plan has been created on an Excel worksheet. One column shows the due date for each completion task. To highlight the overdue deadlines for assigned tasks in color, we need the exact difference between the dates in Excel. To do this, we will use conditional formatting with the RAZNDAT formula.
How to calculate the difference between dates in Excel
It is necessary to highlight in color the names of tasks whose deadlines expire in 7 days. Example of a task plan table:
The final effect of highlighting the end of deadlines after 7 days:
Highlighted green all tasks that are due in 7 days. If you change the values in cell D2, then other tasks will be highlighted.
Helpful advice! In cell D2, you can use a function to get today's date: =TODAY().
Date difference formula in Excel
The formula returns the difference in dates between today and the target date in days. To solve this problem, use the RAZNDAT function in Excel: where can I find this formula?
You will not find this function in the function wizard or even in the FORMULAS panel. It must always be entered manually. The first argument to the function should always be the newest date, and the second argument should always be the highest date. The third argument of the function determines the unit of measurement of the quantity that the =RAZNDAT() function returns. In this case, it is the symbol “d” – days. This means that the function returns the number of days. Next comes the operator<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.
If necessary, you can add a new conditional formatting rule to this range, which will warn us about the end of the deadline 2 weeks in advance. To do this, you need to add a new conditional formatting rule for the range A2:A7 and just slightly change the formula in the new rule:
The formatting color for the second rule can be set to yellow. There must be 2 conditional formatting rules applied to the same range. To check, select the tool: “HOME” - “Styles” - “Conditional Formatting” - “Manage Rules”. Since we execute the top rule first, we should change their order in the window that appears: “Conditional Formatting Rules Manager”. Otherwise, all selected tasks will have a yellow cell fill. Just highlight the first rule and press the down button (CTRL+down arrow), as shown in the figure:
As a result, the plan warns us first two weeks, and then a week before the end of the tasks:
Helpful advice! If there are many rules assigned to the same range, follow the priority hierarchy of the order in which they are executed in the Rule Management Manager. The higher the rule, the higher its execution priority relative to others located below it.
To calculate the duration of time intervals, it is most convenient to use the undocumented function RAZNDAT( ) , English version of DATEDIF().
The RAZNDAT() function is not in the EXCEL2007 help and in Function Wizard (SHIFT+ F3 ), but it works, although not without flaws.
Function syntax:
DATE(start_date; end_date; measurement_method)
Argument start_date must come before the argument final date.
Argument measurement_method determines how and in what units the interval between the start and end dates will be measured. This argument can take the following values:
|
Meaning |
Description |
|
difference in days |
|
|
difference in full months |
|
|
difference in full years |
|
|
difference in full months excluding years |
|
|
difference in days without taking into account months and years |
|
|
difference in days excluding years |
Below is a detailed description of all 6 argument values measurement_method, as well as alternative formulas (the RAZNDAT() function can be replaced with other formulas (though quite cumbersome). This is done in the example file).
In the example file, the argument value is start_date placed in a cell A2 , and the value of the argument final date– in a cell AT 2 .
1. Difference in days ("d")
The formula =DATEDAT(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 length of service it is necessary to use the RAZNDAT() function with caution. Obviously, if an 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 =DATE(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 length of service, it is considered that an employee who has worked all days of the month has worked 1 full month. The RAZNDAT() 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 the MS EXCEL help (see section Calculating age) there is a curved formula for calculating the number of months between 2 dates:
=(YEAR(TDATE())-YEAR(A3))*12+MONTH(TDATE())-MONTH(A3)
If instead of the TDATE() function - the current date, use the date 10/31/1961, and enter 11/01/1962 in A3, then the formula will return 13, although 12 months and 1 day have actually passed (November and December in 1961 + 10 months in 1962) .
3. Difference in complete years ("y")
The formula =DATE(A2;B2;"y") will return the number of full 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 by 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 =DASDAT(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 03/01/2009 and the modified start date 02/01 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_date 01.04.2007, final date 01.03.2009
Result: 11 (months), because The end date 03/01/2009 and the modified start date 04/01 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 by an alternative expression:
=REMAT(C7,12)
In a cell C7
The difference must be in full months (see clause 2).
5. Difference in days excluding months and years ("md")
The formula =DASDAT(A2;B2;"md") will return the number of days between two dates without taking into account months and years. It is not recommended to use the RAZNDAT() 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 starting date are replaced by the year and month of the ending 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 starting date is replaced by the year and month of the ending date)
Example3:start_date 28.02.2009, final date 01.03.2009
Result3: 4 (days) - a completely incomprehensible and INCORRECT result. The answer should be =1. Moreover, the calculation result depends on the version of EXCEL.
EXCEL 2007 version with SP3:
Result – 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 1 day. For the values 12/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 by an alternative expression:
=IF(DAY(A2)>DAY(B2);
DAY(MONTH(DATEMONTH(B2,-1),0))-DAY(A2)+DAY(B2);
DAY(B2)-DAY(A2))
This formula is only an equivalent (in most cases) expression for RAZNDAT() with the md parameter. Read about the correctness of this formula in the section “Once again about the curvature of RAZNDAT()” below.
6. Difference in days excluding years ("yd")
The formula =DASDAT(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 =DATEDAT(A2;B2,"yd") depends on the version of EXCEL.
The formula can be replaced by 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 of RAZNDAT()
Let's find the difference between the dates 03/16/2015 and 01/30/15. The RAZNDAT() function with parameters md and ym will calculate that the difference is 1 month and 14 days. Is it really?
Having a formula equivalent to RAZNDAT(), you can understand the progress of the calculation. Obviously, in our case the number of full months between dates = 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 belongs to March, the previous month is February, and in 2015 there were 28 days in February). After this, it subtracts the start day and adds the end day = DAY(MONTH(DATEMONTH(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?
Let's modify the formula for calculating the days of difference without taking into account months and years:
=IF(DAY(A18)>DAY(B18);IF((DAY(MONTH(DATEMONTH(B18,-1),0))-DAY(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))
When applying the new function, it is necessary to take into account that the difference in days will be the same for several starting dates (see the figure above, dates 01/28-31/2015). In other cases, the formulas are equivalent. What formula should I use? This is up to the user to decide depending on the conditions of the task.
