How to write an IF formula with a date in Excel
Formula (Excel and Google Sheets)
=IF(B2>=$F$1,"New prices","Old prices")Compare the date with a cell or with DATE(), never with a date in quotes: =IF(B2>=$F$1,"New prices","Old prices").
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
A date is stored as a serial number, so comparing two dates is comparing two numbers: later dates are larger. B2>=$F$1 is TRUE from the cutoff day on, which is why A-312, ordered on July 1, gets the new prices.
=IF(B2>=$F$1,"New prices","Old prices")Result"Old prices"
B26/14/2026The value in B2.
$F$17/1/2026The value in F1.
B2>=$F$1FALSEThe >= operator tests at least.
IF(B2>=$F$1,"New prices","Old prices")"Old prices"IF: Returns one value if a test is true, another if false.- logical_test
B2>=$F$1FALSE - value_if_true
"New prices"not used - value_if_false
"Old prices"
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(B2>=$F$1,"New prices","Old prices"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(B2>=DATE(2026,7,1),"New prices","Old prices") | Old prices, New prices, Old prices… | When the date has to live inside the formula, build it with DATE(year, month, day). It works in every locale. |
| =IF(B2>=DATEVALUE("2026-07-01"),"New prices","Old prices") | Old prices, New prices, Old prices… | DATEVALUE turns date text into a date. ISO text (year-month-day) avoids month/day confusion. |
Common mistakes
=IF(B2>="7/1/2026",…)compares a number with text. Text always counts as larger, so the test is FALSE for every date.- Timestamps include a time of day: 7/1/2026 3:00 PM is greater than 7/1/2026. Compare
INT(B2)if times can sneak in. - Dates imported as text compare as text.
=ISNUMBER(B2)should be TRUE for a real date.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Write an IF formula with a date
C2, show "New prices" for orders on or after the cutoff date in F1 and "Old prices" for earlier orders. Fill down to C7.Questions
How do I compare a date with today?
Use TODAY(): =IF(B2<TODAY(),"Past","Upcoming").
How do I test the month instead of a full date?
Use MONTH: =IF(MONTH(B2)>=7,"H2","H1").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.