In this Excel tutorial, we will learn how to
– Use the combination of the IF and DATE functions along with the combination of the IF and TODAY functions to apply date formulas.
– Apply the combination of the IF function and the DATEVALUE function and another way, apply logical operator to compare dates.
– Use the TODAY function to get the exact match of the required date.
– Use Formula with IF Statement to Apply Conditions with Past/Future Date
– Apply the combination of the AND statement and IF function to get the date value.
Therefore, we used Microsoft 365 to execute all the methods. However, all the Excel formulas are added to Excel 2007, Excel 2010, Excel 2013, Excel 2016, and Excel 2021 as well.
Here is the overview image of this article, you will get to know it better once you go through the whole article.
Download Practice Workbook
You may download this workbook for practice.
What Are the Methods to Use Formula with IF, and Date Functions to Compare Dates in Excel?
To use formulas with IF, and DATE functions to compare dates in Excel we will cover all the methods to compare if you can deliver the product on time or not. using the date functions and logical operators.
How to Use IF Function with DATEVALUE Function to Compare Dates?
In this method, we will use the IF function and the DATEVALUE function to determine if we can deliver the product on time or not.
- Initially select cell E5 to insert the formula if the Delivered Date is earlier than the Delivery Date, then the final report will show “Delivered”.
=IF(D5<=DATEVALUE("12-Feb"),"Delivered","Fail to Deliver")
- Lastly, drag down the Fill handle to copy the same formula into the dataset.
Formula breakdown
- DATEVALUE(“12-Feb”), The DATEVALUE function sets the date of delivery of the products.
- IF(D5<=DATEVALUE(“12-Feb”),”Delivered”,”Fail to Deliver”). Here, the IF function determines if the delivery date is earlier than the delivered date or not. If this process fits, then the formula shows Delivered as output; otherwise, Fail to Deliver.
How to Apply IF Function with the TODAY Function?
If the deadline for delivery is today, instead of using any date, you can use the TODAY function.
- In the beginning, select cell E5 and apply the formula to get the output as “Delivered”. Once the process is complete, drag down the fill handle or double-click on the fill handle to copy the formula in other cells.
=IF(D5<TODAY(),"Delivered","not delivered yet")
How to Combine Formula with IF and DATE Functions Simultaneously?
Here, we will use the IF function with the DATE function. This process is quite similar to the method where we used the DATEVALUE function. Instead of using the DATEVALUE function.
- Use the DATE function. So, insert the formula in cell E5 to execute this process.
=IF(D5<=DATE(2023,2,12),"Delivered","Fail to Deliver")
- As already mentioned this process is similar to the earlier method. The DATE Function identifies the date here the order should be Year, Month, and then Date.
How to Use Logical Operator to Compare Date with a Range of Dates?
Here is the overview of the IF function to see how IF function works.
In this method, we will compare two different dates. For instance, if a product’s delivery date is between the arrival date and the delivery date, then we will get the output “Delivered on Time”; otherwise, the product is delayed.
- Inert the formula in cell F5 to get if products are delivered on time or not.
=IF(D5>=E5,"Delivered on Time","Delivery Delayed")
- Here, if the products are delivered before the delivery date, then the output will be Delivered otherwise Delivery Delayed.
How to Compare Dates with a Specific Date Using Logical Operator?
Here, the delivery date is fixed, unlike the previous process. Let’s check out the report in the Report section by applying the formula.
- Now, apply the formula in cell E5 and execute this process.
=IF($B$15>=D5,"Delivered on Time","Delivery Delayed")
- The IF function shows whether the value is TRUE or If the delivery date is before the delivery date, this function is true, so the output will be Delivered on Time. Else, Delivery Delayed.
How to Apply the TODAY Function to Get the Exact Date?
Here, we will use the TODAY function to get the exact date. For instance, if the delivery date is today, then the TODAY function will detect if the product is delivered today or not. If we fail to deliver the product today, then we will consider the output as negative.
- So, select cell E5 and enter the below formula in the cell to get the output.
=IF(D5=TODAY(),"Delivered","Not Delivered")
How to Use Formula with IF Statement to Apply Conditions with Past/Future Date?
To use the formula with an if statement we will apply the IF function and the TODAY function to get the output, but if we use the TODAY function, then we will count the delivery date as today. Here we will add or subtract the required amount of data so that we can get the date of the past or future using the TODAY function.
- Select the cell and insert the formula to cell E5 to execute this process.
=IF(D5<TODAY()+7,"Delivered","not delivered yet")
Formula breakdown
TODAY()+7
- This represents 7 days after the present day. So here the delivery date will be 7 days after today.
IF(D5<TODAY()+7,”Delivered”,”not delivered yet”)
- So, if the product is delivered within today +7 duration then the report will be considered as Delivered otherwise not delivered yet.
How to Use Formula with AND, and IF Functions with Date Value in Excel?
To use the formula with AND and IF functions here, we will apply the AND function and the IF function with dates. Here we will get the output with a range of deadlines; if the product delivers within the range, then the report will be okay; if you deliver the product on time,
- Select cell E5 to insert the formula and execute the process.
=IF(AND(D5>=$C$14,D5<=$C$15),"Delivery On Time","Delivery Delayed")
Formula Breakdown
AND(D5>=$C$14,D5<=$C$15)
- Here, the AND function represents two conditions if the Start Date is equal to or later than the delivery date and earlier than the End Date.
IF(AND(D5>=$C$14,D5<=$C$15),”Delivery On Time”,”Delivery Delayed”)
- If the formula matches then the value is true and the output will be Delivery On Time otherwise Delivery Delayed.
Things to Remember
- Always close the bracket after completing the formula Excel faces some issues running the formula if the brackets are not closed.
- Make sure that the dates are in Date format. If the dates are in the TEXT formula in the workbook then eventually the formulas will not work.
Conclusion
In this article, we learned how to use the if date formula in Excel. The IF function and multiple date functions such as the DATE, TODAY, and DATEVALUE functions in Excel. There are different methods to determine if the final result is achieved or not using the IF function and Date functions Here we cover all the methods for better understanding. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.
Frequently Asked Questions
Q1: How to apply Excel formula if the date is greater than another date?
Ans: If a date is greater than another date, then you need to use the “>” operator to compare the dates.
Q2: What is the date and time formula in Excel?
Ans: You can use the TODAY function as the date formula and the NOW function to calculate the time and date in Excel.
Q3: How to return value If the date is between two dates?
Ans: If the date is between two dates then you can use the formula IF(AND(Lookup Value>=Starting Date,Lookup Value<=Ending Date),”OK”,”Not OK”)
Note: You need to select the cells in the lookup value, starting date, and ending date points.
If Date Formula in Excel: Knowledge Hub
- Return Expected Value If Date Is Greater Than Today
- Return Value If Date Is Within a Range
- Check If Month Is Between Two Dates
- Return Expected Value If Date Is Between Two Dates
- Excel If Date Is Between Range Then Return Value
<< Go Back to Formula List | Learn Excel
