In this video, I demonstrate how to use dates within an IF formula. I want to be able to compare two dates to find if one date is before or after another date. I also want to be able to calculate whether a date is after or before today’s date. You can compare two dates when they are both entered into cells or when one is hard-coded into the formula. Hard-coding a date into an IF formula is not as straightforward as you might think!
Using IF To Find Out If a Date is After Another Date
To find out if a date is after another date you can use the > (greater than) comparison operator. To find out if a date is on or after another date use the >= (greater than or equal to) comparison operator.
Example IF formula to find out if a date is on or after another date:
=IF(C3>=B3,”On time”,”Not on time”)
where C3 is the submission deadline and B3 is the date submitted.
Writing Dates Directly in the IF Formula
There are two ways you can enter dates directly in an IF formula.
Adding Zero to a Date
=IF(B3<=”15/05/2021″+0,”On time”, “Not on time”) -adding zero converts the date to a number
Using the DATEVALUE Function
=IF(B3<=DATEVALUE(“15/05/2021″),”On time”, “Not on time”) – DATEVALUE converts the date to a number
Using IF to Calculate Whether a Deadline Has Been Reached
To compare a date with today’s date in an IF formula use the TODAY function. The following formula tests whether a deadline has been reached.
Where B3 contains the deadline.