Contents
It is not uncommon for an Excel user to compare two dates against each other. This task can appear in a variety of industries. For example, an accountant needs to calculate the number of months, quarters, or years that have passed between different dates. It is useful to calculate the difference between dates and in ordinary life. For example, many people plan personal activities in Excel, set goals and manage a budget, and this feature allows them to better plan for long-term periods of time.
This task can be somewhat confusing at first, since dates cannot at first glance be compared to each other like numbers. But in fact, this is exactly what is done. The reason is that Excel writes each date in numerical format. Therefore, when comparing dates, the program is essentially comparing two numbers. There are several options here, which we will consider in more detail.
2 Popular Ways to Compare 2 Dates in Excel
So, if we need to compare two dates with each other, this can be done by two main methods, depending on the task. Let’s find out what they are. There is nothing complicated in this, the formulas are quite simple and almost do not differ from those used to work with numbers. Do not forget that the date is perceived by the spreadsheet as a number, and is processed in the same way.
The first date is greater than the second, or not
In this case, you need to use a similar formula that is used to check numbers: =A1>A2. If this check is confirmed, then the value “TRUE” is returned to the cell where this formula was written. If the first number is less than the second, then “FALSE” will be written. Similar, only mirror opposite with the formula = A1If, as a result of the check, it turns out that the first value is less than the second, then the logical value “TRUE” will be returned to the cell. If more – “FALSE”.
IF function, value TRUE or FALSE
This is a more complex option, which makes it possible not only to say whether one date is really greater than another, but also to indicate how many days. To do this, you need to click on the cell and write the formula there =IF(A2>B2;”The first date is greater than the second by”&” “&A2-B2&” “&”days”;”The first date is less than the second by”&””&B2-A2&” “&”days”)
Here we check two dates, and based on what we got in the end, we output the result by how many days the first date is more than the second. To do this, we used the & operator, which combines two lines of text with each other and returns the combined result to the cell. Further, the resulting text can be used by itself or in other formulas.
Function RAZDAT – the difference between two dates in days, months and years
Let’s say you work for a pension fund and you want to define seniority in terms of years, days, months, and so on. This can be done using the function RAZNDAT.
Its syntax is elementary.
RAZDAT(start_date; end_date; measure_method)
The only one of these arguments that can really raise questions from an unprepared reader is the method of measurement. With it, we set the units in which the difference between dates will be measured. This can be the number of months, years, or the number of months excluding years. Another option is the number of days, excluding months and years.
It is important to note that in some cases this function may return an incorrect value. This happens when the day of the first date is greater than the day of the second. This situation can be, for example, when you need to calculate the number of days between two dates, the first of which occurred at the end of the month, and the other at the beginning of the next.
Difference of 2 dates in days
To determine the difference between two dates in days, you need to use the following formula.
=RAZDAT(A2,B2,”d”)
But unfortunately, this formula does not work as perfectly as we would like. Let’s take a small example to make it clearer.
Let’s assume that our start date is 23.02.2009/24.02.2009/XNUMX, and the end date is XNUMX/XNUMX/XNUMX.
As a result, the formula will return the value 1, which is equal to the number of days that have passed since the original date (counting from zero). It is precisely because the countdown in this case starts from zero that this formula cannot be used in its pure form, for example, to calculate the number of days that a person has worked. After all, it is obvious that if a person started working on February 23, and stopped working on the 24th, then in fact he worked for 2 days, and not one.
The same story with the number of months. If the start date is February 28th and the end date is March 2008st, and both dates are in 2, the result is XNUMX days. And it is right.
To solve all the problems with displaying months in Excel, there is a special formula WHOLE(), which rounds the number down. Since time is a fraction of a whole day as Excel understands it, these hours will actually be cut off. The same applies to other elements of the date, day, month, which are presented as a fractional part.
By the way, you can use the formula =PURE STUDIES() to determine the number of working days between two dates.
Date difference in full months
To determine the number of full months that have passed from one date to another, you must use the same formula as above, only the third argument must contain the argument m.
That is, the formula is as follows.
=RAZDAT(A2;B2;”m”)
This formula can also give somewhat inaccurate data. Therefore, you should always double-check the values yourself.
Difference in days excluding years
This formula can also be used to determine the number of days between two dates, but does not take into account the number of years that have passed between these two time points. There is this possibility, but it is strongly not recommended to use it, because in this case there may be even more distortion.
This formula will only return the correct result when the correct time is displayed on the computer. Of course, in most modern operating systems, the date and time are determined automatically, but sometimes failures occur. Therefore, before calculating the difference in days without taking into account years, you need to make sure that the correct date and time are set on the computer. Especially if the spreadsheet was sent to another person.
Quick highlighting of distinct values
It’s very simple. If you need to understand if there are different values in an Excel document, you need to find the “Find and Select” button located on the “Home” tab. After that, in the “Select a group of cells” dialog box, select “row differences” as a criterion.
After that, all different values will be highlighted automatically, and you can perform the actions you need with them.
Comparing 2 dates using conditional formatting
You can also use a popular Excel feature called conditional formatting to compare two dates. A different value will be highlighted in a certain color. And you decide how it should be formatted.
To use this option, you need to go to the “Home” tab, and find the corresponding large button there. A list will appear in which we are interested in the “Manage Rules” item. After we select the “Conditional Formatting Rules Manager” option, we need to create a rule using a special button, which is located there.
You must use a formula as your conditional formatting rule. After that, we write down the formula (it is important that the result is true), the formatting style and click on the “OK” button.
This comparison can be made with any type of data, not just dates. Rather, this method is needed in order to highlight data that is different, in order to then write a function for them. But you can initially prescribe the function that is suitable in a particular situation and highlight the true values with a special color. Here, as your heart desires.
Comparing 2 columns for a match
To compare two columns, whether they are the same or not, you need to use conditional formatting. The sequence of actions is generally the same as described above. Suppose we need to perform such operations with the following table.
Each column must be given the correct name first. With it, it will be easier for us to understand which ranges of cells are compared with each other.
The sequence of actions is as follows:
- Go to the “Formulas” tab. There you need to find the button “Assign a name.” It is located at the very top of the toolbar directly below the title of the tab itself.
- A window will open in which we need to set a name for each of the ranges. Let’s say we need to name the first range “Table 1”.
- After that, select the range that we need to call it that. To do this, click on the button located to the right of the field signed as “Range”, select the desired set of cells and click on the “OK” button.
- Actions with the second list are similar. The only exception is to give it a different name. And of course, set the correct addresses of this range.
Recommendation. Use the name field to give the range the desired name much faster.
Finding him is not difficult at all. After we select a suitable set of cells, to the left of the formula bar, you need to click on the field shown in the screenshot. After that, enter a name and press the “Enter” button.
To compare two columns, we need to use the already familiar Conditional Formatting tool. An approximate sequence of actions will be as follows. If we talk about specific steps, then they will greatly depend on what specific task the user needs to perform.
- Select the desired range. After that, we create a formatting rule “Use formula …”.
6 - After that, the formula itself is entered in the corresponding field, and the format of the cells for which this formula turns out to be true is also set.
7 - Next, choose a fill. After that, conditional formatting is selected again, but for a different list. This time, you need to choose a different color.
After we finish all these actions, we will get approximately the following result.
We have applied the function COUNTIF, which checks the list against certain criteria, and then returns the value TRUE or FALSE. If “TRUE” was returned, then the corresponding cells are colored in the colors specified by the user before the conditional formatting was used.
The verification will be carried out sequentially. And this formula can be used in all other cases, not only for comparing two dates with each other. For example, if there are two price lists where you need to compare prices with each other (determine which ones are different, which ones are higher compared to similar items in another price list), then you can also use this formula in conditional formatting.
Formula to compare 2 dates
As you can see, the formula for comparing 2 dates can be any of the above. The user can choose which one to choose. In some cases, it will be necessary to modify them in order to adapt them to specific purposes. For example, when we were faced with the task of determining how many days an employee worked, then we need to add a unit to the existing formula in order to start the countdown not from zero, but from the first day.
There can be many more such nuances. But all these subtleties are comprehended in the process of working with spreadsheets. Unfortunately, even one large article is not enough to cover all aspects of date comparison. However, this is not as difficult a task as it might seem at first glance. And we have just verified this. Good luck.