Comparing dates in Excel

Often, users of a spreadsheet editor need to carry out such a difficult procedure as comparing dates. This action can be implemented in various ways. In the article, we will analyze in detail all the methods that allow you to compare dates in a spreadsheet editor.

Processing time in a spreadsheet editor

The spreadsheet editor treats time and date as numeric data. The program converts this information in such a way that one day is equal to 1. As a result, the time indicator is a fraction of one. For example, 12.00 is 0.5. The spreadsheet editor converts date indicators into a numeric value, which is equal to the number of days from January 1, 1900 to the specified date. For example, if the user converts the date 14.04.1987/31881/31881, then it will have the value 2. In other words, XNUMX days have passed from the original indicator. This mechanic is applied when calculating time values. In order to calculate the number of days between XNUMX dates, it is necessary to subtract a smaller time indicator from a larger time indicator.

Using the DATE statement in a table editor

The general view of the operator looks like this: DATE(year, month, day). Each of the arguments is required to be written in the operator. There are two ways to set an argument. The first method involves the usual input of numeric values. The second method involves entering the coordinates of the cells in which the necessary numerical information is located. The first argument is a numeric value from 1900 to 9999. The second argument is a numeric value from 1 to 12. The third argument is a numeric value from 1 to 31.

For example, if you specify a numerical value greater than 31 as the day, then the extra day will move to another month. If the user enters thirty-two days in March, he will end up with the first of April.

An example of using the operator looks like this:

An example of specifying a larger number of days in June:

An example showing the use of cell coordinates as arguments:

Using the RAZDAT operator in a spreadsheet editor

This operator returns between 2 date values. The general view of the operator looks like this: RAZDAT(start_date; last_date; code_for_designation_of_count_units). Types of calculation of intervals between two specified date indicators:

• “d” – displays the final indicator in days;
• “m” – displays the total in months;
• “y” – displays the total in years;
• “ym” – displays the total in months, excluding years;
• “md” – displays the total in days, excluding years and months;
• “yd” – displays the total in days, excluding years.

In some versions of the spreadsheet editor, when applying the extreme 2 arguments, the operator may display an error. In this case, it is more appropriate to use other formulas.

An example showing the operation of the operator:

In the 2007 spreadsheet editor, this operator is not in the reference, but you can still use it.

Using the YEAR operator in a spreadsheet editor

This operator allows you to return the year as an integer value corresponding to the specified date. The numeric value is displayed in the range from 1900 to 9999. The general form of the YEAR operator has 1 argument. The argument is a numeric date. It must be written using the DATE operator, or output the final indicator of the calculation of any other formulas. An example showing the operation of the operator:

Using the MONTH operator in a spreadsheet editor

This operator allows you to return the month as an integer value corresponding to the specified date. The numeric value is displayed in the range from 1 to 12. The general form of the MONTH operator has 1 argument. The argument is the date of the month, written as a numeric value. It must be written using the DATE operator, or output the final indicator of the calculation of any other formulas. It is worth noting that a month written in text form will not be processed correctly by the spreadsheet editor. An example showing the operation of the operator:

Examples of using the DAY, WEEKDAY, and WEEKDAY operators in the spreadsheet editor

This operator allows you to return the day as an integer value corresponding to the specified date. The numerical value is displayed in the range from 1 to 31. The general form of the DAY operator has 1 argument. The argument is the date of the day, written as a numeric value. It must be written using the DATE operator, or output the final indicator of the calculation of any other formulas. An example showing the operation of the operator:

The operator, which has the name WEEKDAY, allows you to return the ordinal number of the day of the week of a given date. By default, the operator considers Sunday as the 1st day of the week. An example showing the operation of the operator:

The operator, which has the name NOMWEEK, allows you to display the ordinal number of the week in a given date. An example showing the operation of the operator:

For example, May 24.05.2015, XNUMX is the twenty-second week of the year. As it was written above, the program considers Sunday as the first day of the week.

The second argument is 2. This allows the spreadsheet editor to consider Monday as the start of the week (only within this formula).

The TODAY operator is used to set the current date. This operator has no arguments. The TDATE() operator is used to display the current date and time.

Conclusion and conclusions about comparing dates in a spreadsheet editor

We found out that there are many ways and operators to compare two dates in a spreadsheet editor. The most common option is to use the RAZNDATA operator, which allows you to return the difference between two dates. Additionally, you can use similar formulas to return day, month, and year values. Each user can independently choose for himself the most convenient way to compare dates in a spreadsheet editor.