While working in Microsoft Excel sometimes we need to check if a date is before another date or not? On the basis of comparing between two dates, we create a final status report. Sometimes it seems difficult to compare dates when there are several conditions. But from today it won’t be a problem anymore. Today in this article, I am sharing with you how to compare if date is before another date in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
6 Quick Methods to Compare If Date Is Before Another Date in Excel
In the following article, I have shared 6 simple and easy methods to compare if date is before another date in excel. Suppose we have a dataset of some Student’s Names. They were given an assignment to finish. So we have their Submission Date and Submission Deadline of their assignment. Now we are going to compare if the submission date is before the deadline date or not?
1. Use Formula to Compare If Date Is Before Another Date
With a simple mathematical formula, you can compare if the date is before another date. Follow the steps below-
- Select a cell to write a formula. Here I have selected cell (E5).
- Apply the following formula-
- Press the Enter button and you will get the result. Here, it returned “True” as the “Submission Date” is less than the “Submission Deadline”. Otherwise, the result will be “False”.
- Simply, drag the “fill handle” down to fill the cells with desired output.
- Finally, we have successfully compared two dates if one is before with another date or not. Simple isn’t it?
2. Utilize IF Function to Compare If Date Is Before Another Date
While comparing dates you might want to get other statements besides “True” and “False”. For that, you can use the IF function in excel to compare both dates in excel.
- Starting with, choose a cell (E5) to apply the formula.
- Put the formula down-
=IF(C5<=D5,"On time","Late submission")
- The IF function checks if the condition is met or not and then returns a defined statement based on the given condition.
- Hence, click the Enter button and pull the “fill handle” down to get the desired output.
- In summary, we have compared two dates and got our output in the status column.
3. Insert Date in Formula to Compare If Date Is Before Another Date
In some cases, you might have a common date to compare with all the other dates. In those circumstances, you can insert the date inside the formula. Below I have shared the steps. Please follow-
- First, choose a cell (D5) to apply the formula.
- Write the formula down-
- After that, hit Enter and drag down the “fill handle”.
- In conclusion, the dates are compared using a simple formula.
4. Apply DATEVALUE Function to Compare If Date Is Before Another Date
You can also do the same task by applying the DATEVALUE function in excel. The DATEVALUE function converts a date to a text string as a serial number.
Suppose we have a dataset with some dates just like the following screenshot.
- Choose a cell (D5) and then put the following formula down-
- Gently, press Enter and drag down the “fill handle”.
- Here we have successfully compared if date is before another date utilizing the DATEVALUE function.
5. Perform TODAY Function to Compare If Date Is Before Another Date
The TODAY function in excel provides the current date in a string. Simply, you can compare any date with the current date. Here in this method, we put the current date with the help of the TODAY function.
- In the same fashion, select a cell (D5) to apply the formula-
- Then, click the Enter button and drag down the “fill handle” to fill all the cells.
- Finally, we compared the date from the dataset with our current today’s date without any hesitation. As all the dates from the list are before today’s date thus the output is “True” for all the cells.
6. Combine IF and TODAY Functions to Compare If Date Is Before Another Date
If you want you can also use the combination of the IF and TODAY functions to compare if date is before another date. Here the TODAY function will provide today’s date into the string and the IF function will check the statement and give the result according to the statement.
- In the same way, we will choose a cell (D5) and apply the following formula down-
- Hence, hit the Enter button.
- Now, drag down the “fill handle” to get the final output.
- Therefore, we have reached our destination by comparing date if it is before another date in excel.
Things to Remember
- In method 3, after applying formula sometimes “#VALUE!” error may occur. In order to avoid errors don’t forget to use quotation marks (“”)at the start and end of the date.
In this article, I have tried to cover all the methods to compare if date is before another date in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
- How to Use COUNTIF for Date Greater Than 30 Days in Excel
- Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
- How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
- Excel Formula If Date Is Greater Than 2 Years (3 Examples)
- Conditional Formatting Based on Date Older Than 1 Year in Excel
- Excel Conditional Formatting for Date Within 3 Months (3 Methods)