How to calculate Expiry Date in Excel

In this post, we will show you how to calculate the expiry date in Microsoft Excel. Calculating expiry dates is a common requirement when working with Excel, especially for tracking inventory, managing subscriptions, or handling compliance-related tasks. Whether it’s food, medicine, software licenses, or warranties, keeping track of expiry dates is essential to prevent serious consequences.

Excel offers several easy ways to calculate expiry dates based on days, months, or business rules. In this post, we’ll explore different ways to automate expiry date calculations in Excel.

How to calculate expiry date in Microsoft Excel

To calculate the expiry date in Excel, you may use the following methods:

Add days directly
Use EDATE for monthly intervals
Use EOMONTH for end-of-month expiry
Use WORKDAY (for business expiry dates)
Use DATEDIF for time left until expiry

Let us see this in detail.

1] Add days directly

If the expiry period is defined in days, you can calculate the expiry date by simply adding the number of days to the starting date using the + operator.

For example, if cell A2 contains the manufacturing date of a product, say 01-04-2025, and the product expires after 30 days, you can apply the following formula in cell B2 to calculate the expiry date:

=A2+30

This formula will return 01-05-2025, which is the calculated expiry date.

Let me explain why this works. In Excel, every date is stored as a serial number, starting from January 1, 1900, which is serial number 1. So, January 2, 1900, is serial number 2, January 3, 1900, is serial number 3, and so on. This system allows Excel to treat dates as numbers and perform arithmetic operations, such as adding or subtracting days.

2] Use EDATE for monthly intervals

If the expiry period is defined in months, you can calculate the expiry date using the EDATE function. The EDATE function adds or subtracts a specific number of calendar months to a given date. It automatically adjusts for different month lengths (e.g., February vs. July) and even handles leap years.

In the above example, if the product expires after 3 months of its manufacturing date, you can apply the following formula in cell B2:

=EDATE(A1, 3)

This formula will return 01-07-2025, which is the calculated expiry date.

3] Use EOMONTH for end-of-month expiry

If the expiry date falls on the last day of a month, you can use the EOMONTH function. This function calculates the exact end of a month by taking the starting date and shifting forward (or backward) the number of months you specify. It then returns the final day of that resulting month (regardless of how many days that month has).

In the same example, if the product expires 3 months later, at the end of that month, you can apply the following formula in cell B2 to calculate the expiry date:

=EOMONTH(A2, 3)

This formula will return 31-07-2025, which is the last day of the third month after the manufacturing date.

4] Use WORKDAY (for business expiry dates)

If the expiry period is based on working or business days (excluding weekends or any date identified as a holiday), the WORKDAY function is the best option. It returns a date that is a specified number of working days before or after a given start date.

In the same example, if the product expires 30 working days later, you can apply the following formula in cell B2 to calculate the expiry date:

=WORKDAY(A2, 30)

This formula will return 13-05-2025 (Tuesday), which is the 30th business day after 01-04-2025, excluding Saturdays and Sundays. You can also add a list of holidays as a third argument to exclude public holidays from the count.

5] Use DATEDIF for time left until expiry

If you want to calculate how much time is left until a certain expiry date, you can use the DATEDIF function. This function returns the difference between two dates in terms of days, months, or years.

For example, if cell A2 contains the expiry date 01-07-2025, you may apply the following formula in cell B2 to calculate the number of days remaining until expiry:

=DATEDIF(TODAY(), A2, "d")

Since TODAY() returns the current date, this formula shows exactly how many days are left (“d” for days) until the date in A2.

For a more detailed format, you can combine multiple DATEDIF functions.

Bonus Tip: Use IF for conditional expiry handling

If you want to display “Expired” if the expiry date in cell A2 has already passed, or “Valid” if it’s still valid, you can use the IF function as follows:

=IF(A2 < TODAY(), "Expired", "Valid")

I hope you find this useful.

Read: How to ring an alarm or set a reminder in Excel.

How to calculate expiration date?

To calculate an expiry date in Excel, you may use the EDATE function, EOMONTH function, or WORKDAY function. These functions help you work with date and time in your worksheets, allowing you to calculate future dates for your specific needs.

How to calculate date duration in Excel?

You can use the DATEDIF function or the NETWORKDAYS function to calculate date duration in Excel. The DATEDIF function calculates the difference between two dates in calendar units (days, months, or years), whereas the NETWORKDAYS function calculates the number of working/business days between two dates.