One of our customer required to calculate the item’s reminder date automatically based on the item’s expiry date and display the item that its reminder date is placed on current month & year.
The reminder date will be the date which is 3 months before the expiry date. For example:-
Part 1: Steps to calculate the reminder date automatically
- From the list, click on “List Settings” which is under ‘LIST’ tab menu.
- Scroll down the page and find the ‘Columns’. Then, click on “Create column” to create a new column for reminder date.
- Fill in the column name and choose the “Calculated (calculation based on other columns)” as its column type.
- Scroll down the page until you found the ‘Additional Column Settings’. Then, type in the formula below into the ‘Formula’ field.
Follow by selecting “Date and Time” as the returned data type from formula.
=DATE(YEAR([Expiry Date]),MONTH([Expiry Date])-3,DAY([Expiry Date]))
- Click on “OK” button to create the column for reminder date.
- After you add the new item, the reminder date will be automatically calculated, as the picture shown below:
to be continued …