# How to write an IF formula with a date in Excel

Compare the date with a cell or with DATE(), never with a date in quotes: =IF(B2>=$F$1,"New prices","Old prices").

**Excel:** `=IF(B2>=$F$1,"New prices","Old prices")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | Order date | Price list |  | Cutoff | 2026-07-01 |
| A-311 | 2026-06-14 |  |  |  |  |
| A-312 | 2026-07-01 |  |  |  |  |
| A-313 | 2026-06-30 |  |  |  |  |
| A-314 | 2026-08-09 |  |  |  |  |
| A-315 | 2026-05-22 |  |  |  |  |
| A-316 | 2026-07-15 |  |  |  |  |

Formula in C2, filled to C7: `=IF(B2>=$F$1,"New prices","Old prices")`

Result: Old prices; New prices; Old prices; New prices; Old prices; New prices

## 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.

## Other ways

- `=IF(B2>=DATE(2026,7,1),"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")`: 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.

## 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")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-formula-with-a-date