The expiration date can be calculated in many ways, i.e. days, months & years. Let us discuss all one by one with example and Figures.
Expiry Date Calculation in Days
If a company makes a product at the date of 01/01/2018 and the validity period of the product is 60 days, then the expiry date of a product can be calculated simply by adding days (60) in the production date (01/01/2018). This will give you expiry date of 02/03/2018.
In Figure.1 below Cell F8 shows the date of production, whilst Cell G8 is showing the number of days where the product will remain valid.
Mirror in the Figure.1 shows formula used in to calculate expiry date.
Figure .1 – Calculate Expiration Date (Days)
Monthly Calculation of Expiry Date
If Product validity is involved in months, the expiry date can be calculated by applying EDATE formula to the numbers.
In Figure.2 below Cell E5 denotes the date when the product is made and Cell F5 denotes the period for product validity.
Figure .2 – Calculate Expiration Date (Months)
Yearly Calculation of Expiry Date
Date formula is used to calculate expiry date of a product if there is one or more years’ validity involves. Figure.3 below shows multiple color lines; yellow, purple and red lines represent the year, month and day respectively. In Figure.3 given below, you will notice Cell E5 is commonly used for all three formula sections. This is because Cell E5 includes day, month and year.
Another noticeable thing is that Cell F5, which represents 2 years as a validity period of the product added with Cell E5. Both represent as the yellow line in the Figure.
Blue Mirror in Figure.3 is showing formula used in the formula bar.
Figure .3 – Calculate Expiration Date (Years)
Calculation of Ending Date of Month
Another important formula that is mostly used in the working of months is EOMONTH formula. By applying the formula, ending date of the running month could be calculated easily.
In Figure.4 below Cell G5 is representing starting date of the month and Cell H5 is representing Months added to 01/01/2018.
Figure .4 – Calculate Expiration Date with Month Ended Date Calculation
Figure.4 is showing 0 in “Months Added Column” this means we want to find ending date of running month, that’s why we didn’t add any other number more than 0.
In order to find next month’s ending date, replace 0 to 1 in Months Added column.
This will be the addition to the existing formula.