Skip to main content

How to calculate expiration dates in Excel?

Author: Kelly Last Modified: 2020-04-28

For example you have a membership card, and it will be expired after 10 weeks. How to quickly calculate the expiration date in Excel? This article will introduce two tricky ways to solve it.


Calculate expiration dates with Excel formulas

We can apply formulas to calculate expiration dates in Excel easily. For example the start date is 8/14/2015 in Cell A2, and the expiration period is 3 months, here I will guide you to calculate the expiration date as follows:

1. Select a blank cell, type below formula into it, and then drag the Fill Handle to the range as you need.

=EDATE(B3,3)

 

2. Sometimes the calculated expiration dates may not display in date format. If so, please keep selecting the calculated expiration dates, and click the Home > Number Format drop down list > Short Date or Long Date. See screenshot:

Now the calculated expiration dates are showing in the proper date formatting.

Notes:

1. In the formula =EDATE(B3,3), 3 means "3 months".

2. Sometimes, the expiration period may be not measured as Month, such as 45 days, 2 years, or 10 weeks. In these conditions, please calculate expiration dates with proper formulas in below table:

No. Expiration Period Formula Expiration date
1 45 Days =B3+45 9/28/2015
2 3 Months =EDATE(B3,3) 11/14/2015
3 2 Years =DATE(YEAR(B3)+2,MONTH(B3),DAY(B3)) 8/14/2017
4 10 weeks =B3+10*7 10/23/2015

Calculate expiration dates with Kutools for Excel

Kutools for Excel's Date & Time Helper feature also provides an quite easy way to quickly calculate expiration dates in Excel. Please do as follows:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

1. Select a blank cell for locating the expired date, and click the Kutools > Formulas Helper > Date & Time Helper to enable this feature.

2. In the opening Date & Time Helper dialog, tick the Add option in the Type section, specify the start date in the Enter a date or select a date formatting cell box, specify the expiration period as you need, and click the OK button.

Note: In my example, i suppose the expiration period is 3 months, therefore I enter 3 in the Month box in above dialog.

3. Keep selecting the formula cell, and drag its Fill Handle for applying this formula to the range as you need.

This Formula collection also provides formulas to calculate expiration dates if expiration period is measured with year, week, days, etc. See following screenshots:

If expiration period is 2 years:

If expiration period is 26 weeks:

If expiration period is 180 days:

If expiration period is 1 year 6 months and 20 days:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations