Forum Discussion
Excel Formulas
can anyone help me please, i need to create a spreadsheet with a list of people with expiry dates for a particular certification expiry date, i would like to put in a formula to give me a 6 month warning for each entry prior to their expiry, so they have plenty of time to renew before expiry.
5 Replies
6 months back from the date is
=EDATE(A1,-6)
with formatting resulting cell as date
- Sorry about that. I left off a closing parenthesis after the first A1. It should read: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))
If your expiry date were in A1 and had a value of 11/30/2018, you could use a simple formula like:
=DATE(YEAR(A1,MONTH(A1)-6,DAY(A1))
thanks for your response jimfikes, the formula is not working, its telling me i've entered too many arguments for this function.
Any advice is much appreciated!
to get the current date (today), we can use
=TODAY()
to get the year of today
=YEAR(TODAY())
to get the month of today
=MONTH(TODAY())
to get the day value of today
=DAY(TODAY())
if you want a particular date
=DATE(2018,12,3)
so, to use DATE function to set today
=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
but you need the date value of expiry date which is in cell A1
=DATE(YEAR(A1),MONTH(A1),DAY(A1))
and a date value of a half year before the expiry date
=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))
