Excel Formulas to Track Expiration Dates (Free File)

The Excel formula that tracks an expiration date is =E2-TODAY(): the use-by date minus today gives the days left, and a negative number means it's expired. Wrap that in an IF to turn the number into a word (Expired, Use now, Use first), use conditional formatting to color the row, and add a SUMPRODUCT that totals what's about to expire in dollars. Below is each formula taken apart, on a real week, with the file to download.

The short version

• One row per lot (each delivery of each item), with its use-by date in a column of its own.
• Days left: =E2-TODAY(). Status: a nested IF on days left. Both copied down the whole column.
• Color the whole row with conditional formatting, using a formula that looks at the status column.
• Two numbers at the top: how many lots expire in the next seven days, and what they're worth.
• Or let the sheet keep itself: TaroStack takes stock off each lot as it's used and warns you before a date. More on that below.

The sheet the formulas live on

Here's a small kitchen's dated stock on Tuesday, September 29, 2026. It's Windward Roots, the made-up taro business I use in these examples.

Columns A to K in the file are item, lot, received, shelf life, use-by, quantity, unit, unit cost, days left, status and value. Download it here. It's a normal Excel file with no macros, and it opens the same in Google Sheets, LibreOffice and Numbers.

Formula 1: work out the use-by date

Some things arrive with a date printed on them. Some don't: the taro paste is made in the kitchen and keeps seven days, and fresh yeast keeps about two weeks from delivery. For those, the use-by is the received date plus the shelf life, because Excel stores dates as numbers of days:

=C2+D2

In the file it's =IF(OR(C2="",D2=""),"",C2+D2), which stays blank until both cells are filled in. When the package has a printed date, type the date over the formula. The cream and the cream cheese above are typed.

Formula 2: days left

=E2-TODAY()

TODAY() is today's date, and it updates every time the file opens. If the answer shows up as a date (something like Jan 2, 1900), the formula is fine and the cell is formatted as a date. Format it as a number.

Formula 3: turn the number into a word

=IF(I2<0,"Expired",IF(I2<=3,"Use now",IF(I2<=14,"Use first","OK")))

Excel reads it left to right and stops at the first test that's true. So a lot with 2 days left is "Use now", even though 2 is also less than 14. Change the 3 and the 14 to suit what you make. A creamery might use 2 and 5, and a chocolate maker 30 and 90.

Formula 4: color the whole row

Select A2:K200, then Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format". Add three rules, in this order:

=$J2="Expired"
=$J2="Use now"
=$J2="Use first"

Give them dark red, red and amber fills. The $ before the J matters: it keeps every cell in the row looking at that row's status, so the whole row lights up and not one cell. In Excel's rule manager, tick "Stop If True" on each so an expired row doesn't also pick up the amber.

If you'd rather go by the number, =AND(ISNUMBER($I2),$I2<=3) does the same job without the status column.

Formula 5: count what needs using this week

=COUNTIFS(I2:I200,">=0",I2:I200,"<=7")

That's lots with zero to seven days left. On the sheet above it's 3: the paste, the yeast and the cream. For expired lots, =COUNTIF(I2:I200,"<0") gives 1.

Formula 6: what it's worth

=SUMPRODUCT((I2:I200>=0)*(I2:I200<=7)*F2:F200*H2:H200)

Each test in brackets gives 1 or 0, so the formula adds up quantity times unit cost only for lots with 0 to 7 days left. Here that's 14 × $3.84 + 2 × $2.75 + 8 × $3.25 = $85.26 of stock to use, move or discount this week, and the cream cheese, 3 × $3.40 = $10.20, is already lost. I'd put that one number at the top of the sheet. It's the one that tells you whether this week needs attention.

Days left doesn't say whether a lot will actually be used up in time, at the rate you go through it. That's a harder formula, and it's worked through in how to keep track of expiring ingredients in a bakery.

Two more that people ask about

The soonest date for one item

In Excel 2019 or later, Microsoft 365 and Google Sheets:

=MINIFS(E:E,A:A,"Cream cheese")

In older Excel, =MIN(IF(A2:A200="Cream cheese",E2:E200)), entered with Ctrl+Shift+Enter (Microsoft). The downloadable file leaves these out on purpose, so it works in any version.

Is that a real date?

=ISNUMBER(E2) says TRUE for a real date and FALSE for text that only looks like one, which is what you get when dates are pasted in from a supplier's email. Every formula above fails on a text date. Retype it, or convert it with =DATEVALUE(E2).

When the formulas give odd answers

The file dodges the last one: its example dates are worked out from today's date, so the colors show whenever you open it. Type over them and they become ordinary dates.

Where this stops working

The formulas are the easy part, and they're right every time. The sheet goes wrong somewhere else: in the quantity column. Nothing in Excel knows that Tuesday's batch used 6 kg of that taro paste, so the 14 kg stays 14 until somebody changes it by hand, and the $85.26 is only as true as the last time they did. Skip a week and it's fiction.

It also can't see which lot the kitchen actually reached for. If the newer cream went into the batch, the sheet still thinks the older one is waiting, right up until it's thrown out. The full routine for keeping the numbers honest is in how to build an inventory list with expiration dates, and why the soonest date should always go first is in FEFO vs FIFO.

How TaroStack does it

If you've built this sheet, you've already done the hard part, which is deciding to track dates by lot. TaroStack keeps the same list, except nobody retypes the quantities. Each delivery comes in as a lot with its date. When Ana records a batch, the recipe takes the taro paste from the lot that expires first, so the lot that would have been thrown out is the one that gets used, and its quantity drops on its own.

The warnings come to you rather than waiting for someone to open a file. Lots about to expire show up in the app and by email, one at a time or as a daily summary, and expired or held stock stops counting as available, so it can't be sold or planned into a batch. There's also a forecast of what will expire unsold at the rate you're using it, and what that will cost, which is the question the $85.26 was trying to answer.

Bring this sheet with you: items and opening stock with lots and dates import from a spreadsheet, with a preview of every row. Lots and expiry are on every plan, from $49 a month, and the first 30 days are free. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do I track expiration dates in Excel?

Give each delivery its own row with its use-by date, then add =E2-TODAY() in the next column to count the days left. A nested IF turns the number into Expired, Use now or Use first, and conditional formatting colors the row. Sort by days left and the top of the sheet is what to use first.

Is there a free expiration date tracking Excel template?

Yes, the one on this page, with no email and no sign-up. It has every formula above already in place down to row 200, the row colors, the counts and the money at risk, and a tab that explains each formula. If you'd rather use an app than a file, there's a comparison in an app to track expiration dates.

What is the formula for days until expiration in Excel?

=E2-TODAY(), where E2 holds the expiration date. Format the answer as a number. For whole months instead of days, =DATEDIF(TODAY(),E2,"m") works, but it gives an error once the date has passed, so days are simpler.

How do I highlight expired dates in Excel?

Conditional Formatting > New Rule > "Use a formula", with =$E2<TODAY() for the use-by column in E, applied to the whole table. The $ makes the entire row change color, not only the date.