Excel Formula: Calculate expiry date
If there are hundreds of food products in a store with different produce dates and warranty periods, you may want to know the expiry date of each product. Here, in this tutorial, it provides some formulas to calculate expiration date in Excel.
Here are some different expiration calculating types, the tutorial will introduce and explain the formulas one by one
If you want to follow along with this tutorial, please download the example spreadsheet.
1 Calculating expiration date n days in the future
Generic formula:
| start_date+warranty |
Arguments
| Srart_date: the start date of the product. |
| Warranty: the total period that guarantee quality. |
How this formula work
For instance, cell C3 contains the start date of a product whose warranty period is 100 days, please use below formula to get the expiration date.
| =C3+100 |
Press Enter key.
2 Calculating expiration date in end of current month
Generic formula:
| EOMONTH(start_date,0) |
Arguments
| Srart_date: the start date of the product. |
Return value
This formula returns a serial number. In Excel, dates are stored as serial numbers for calculation. January 1, 1900 is defined as 1, similarly, July 3, 2020 is defined as series number 44015.
How this formula work
For instance, cell C4 contains the start date of a product which guarantees quality until the end of current month, please use below formula to get the expiration date.
| =EOMONTH(C4,0) |
Press Enter key to get a serial number.
Then format the serial number as date format by clicking Short Date from the drop-down list of Number Format under Home tab.
Explanation
EOMONTH function: Get the last day of a given number of months in future or past.
Return value
This formula returns a serial number. In Excel, dates are stored as serial numbers for calculation. January 1, 1900 is defined as 1, similarly, July 3, 2020 is defined as series number 44015.
3 Calculating expiration date n months in the future
Generic formula:
| EDATE(start_date,months) |
Arguments
| Srart_date: the start date of the product. |
| Month: the number of months for keeping quality. |
Return value
This formula returns a serial number. In Excel, dates are stored as serial numbers for calculation. January 1, 1900 is defined as 1, similarly, July 3, 2020 is defined as series number 44015.
How this formula work
The cell C5 contains the start date of a product whose shelf life is 2 months, please use below formula to get the expiration date.
| =EDATE(C5,2) |
Press Enter key to get a serial number.
Then format the serial number as date format by clicking Short Date from the drop-down list of Number Format under Home tab.
Explanation
EDATE function: Add n months for a date.
Note:
If the item’s expiration date is n years in the future, you also use the formula EDATE(start_date,month). For instance, the start date is 3/1/2020, the warranty period is 1 year (12 months), use the formula:=EDATE(C6,12).
Relative Formulas
- Calculate days remaining in month
In this tutorial, it provides a formula to quickly calculate the days remaining in month in Excel. - Calculate days remaining from today
Sometimes, you may want to know how many days remaining until expiration from today of each food product. In Excel, there is a formula that can help you to quickly get the remaining days. - Calculate Days Remaining Between Two Dates
Here, this tutorial provides a formula to quickly calculate the left days between two dates. - Calculate Network Time With Break In A Timesheet
It provides the formula which uses the MOD function to calculate the network time with breaks in Excel.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
