How to Compare Dates in Excel
Table of Contents
Many times we are facing issues in working with Dates in Microsoft excel.
When we see dates in excel we think those are the same. There is a possibility that these might be different values in the backend.
In this tutorial, we will learn some techniques that can be used to compare dates in excel.
this could be useful when we have to compare two dates in cells are the same or not. We can check if one date is greater than or less than the other date.
How does Excel Stores Dates In the Cells?
First Let’s understand how dates and time values are stored in Excel.
Dates are stored in excel’s backend as a whole number and time is stored as a decimal numbers.
The dates start in Excel from 01 Jan 1990, which means that the value 1 formatted as a date,results will show you 01-01-1990.
Similarly, 44652, would represent 01 Apr 2022 (which means that 44652 days have passed between 01 Jan 1900 and 01 Apr 2022).
While Time is stored as decimal values, where 0.5 would represent 12 hours and 0.75 would represent 18 hours.
So if we have the value 44652.5 in a cell, this would represent 01 Apr 2022 at 12:00 PM.
If you enter Jan 01, 2022 in a cell in Excel, this would be treated as a text string and not as a valid date format.This is also the reason that not all date formats are acceptable formats in Excel.
Comparing Dates in Excel
Now that we have an understanding of dates and time values are handled in the Excel backend, let’s see how to compare two dates in Excel.
Compare whether the Dates are the same or not
In the below table, We have a dataset where we have two sets of dates in two columns. Now I want to check whether these dates are the same or not.
This could be done through a simple = (equal to) operator.
The above-given formula would return TRUE if the compared dates are the same, and FALSE if they are not.
Since dates are stored as numeric values, when we use this formula, Excel simply checks whether the date numeric value is the same or not.
Comparing dates in Excel is just like comparing two numbers.
Dates can be in two different formats and yet to be equal, as the backend numeric value of these dates are the same.
In case If enter a date as a string, then we won’t be able to compare these dates.
Compare Dates Using If Formula :
Below, we have dates in two different columns, and I want to check whether the dates in column B are greater than column A dates, or dates are in column B occurred before column A dates.
Lets Assume the finance company commits the payment dates, but might be actual payment date vary from the committed date, and in some cases, it is on the time.
So lets compare dates using If formula.
This will help us to identify whether the payment was received before or after the committed date.
Below is the formula that will do this:
The above-given formula is compared dates using less than or greater than the operator.