To calculate an expiration in the future, you can use a variety of formulas. In the example shown, the formulas used in column D are:
=B5+30// 30 days=B5+90// 90 days=EOMONTH(B7,0)// end of month=EDATE(B8,1)// next month=EOMONTH(B7,0)+1// 1st of next month=EDATE(B10,12)// 1 year
How these formulas work
In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. This means that January 1, 2050 is the serial number 54,789.
If you are calculating a date n days in the future, you can add days directly as in the first two formulas.
If you want to count by months, you can use the EDATE function, which returns the same date n months in the future or past.
If you need an expiration date at the end month, use the EOMONTH function, which returns the last day of the month, n months in the future or past.
An easy way to calculate the 1st day of a month is to use EOMONTH to get the last day of the previous month, then simply add 1 day.
To calculate the days until an expiration date, you can use a simple formula based on the TODAY function. In the example shown, the formula in C6 is: = B6 - TODAY () How this formula works Dates in Excel are simply serial numbers. In the standard...
To calculate the number of days before a certain date, you can use subtraction and the TODAY function. In the example, D5 contains this formula: = B4 - TODAY () How this formula works In Excel, dates are simply serial numbers. In the standard date...
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for...
The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.