# How to check if a date is between two dates in Excel

Test both ends with AND: `=IF(AND(B2>=$F$1,B2<=$F$2),"Yes","No")`.

**Excel:** `=IF(AND(B2>=$F$1,B2<=$F$2),"Yes","No")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Booking | Check-in | In promo? |  | Promo start | 2026-06-01 |
| BK-71 | 2026-05-31 |  |  | Promo end | 2026-06-30 |
| BK-72 | 2026-06-01 |  |  |  |  |
| BK-73 | 2026-06-18 |  |  |  |  |
| BK-74 | 2026-06-30 |  |  |  |  |
| BK-75 | 2026-07-01 |  |  |  |  |
| BK-76 | 2026-06-09 |  |  |  |  |

Formula in C2, filled to C7: `=IF(AND(B2>=$F$1,B2<=$F$2),"Yes","No")`

Result: No; Yes; Yes; Yes; No; Yes

## How it works

AND is TRUE only when both tests pass: on or after the start date and on or before the end date. The boundary days count, so June 1 and June 30 are inside the promotion and May 31 and July 1 are not.

## Other ways

- `=AND(B2>=$F$1,B2<=$F$2)`: TRUE or FALSE, for filters and conditional formatting. (Returns TRUE/FALSE.)
- `=IF(MEDIAN($F$1,B2,$F$2)=B2,"Yes","No")`: MEDIAN returns the middle value of three, which is the date only when it lies inside the range.
- `=IF((B2>=$F$1)*(B2<=$F$2),"Yes","No")`: Multiplying the two tests is the same as AND, and it also works inside array formulas.

## Common mistakes

- With timestamps, June 30 at 3:00 PM is later than June 30 itself and fails `<=$F$2`. Use `B2<$F$2+1`.
- Dates typed into the formula as text, like `B2>="6/1/2026"`, compare a number with text and give the wrong answer. Keep the dates in cells or use DATE(2026,6,1).

## Questions

**How do I count the dates between two dates?**

`=COUNTIFS(B2:B7,">="&F1,B2:B7,"<="&F2)`.

**How do I check if two date ranges overlap?**

They overlap when each one starts before the other ends: `=AND(start1<=end2,start2<=end1)`.

**How do I check if today falls between two dates?**

Put TODAY() in the test: `=IF(AND(TODAY()>=F1,TODAY()<=F2),"Promo on","Promo off")`.

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/check-if-a-date-is-between-two-dates