Dec 02 2018 10:03 AM
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.
Dec 02 2018 01:18 PM
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))
Dec 02 2018 05:23 PM
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!
Dec 03 2018 06:22 AM
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))
Dec 03 2018 06:46 AM
Dec 03 2018 02:48 PM
6 months back from the date is
=EDATE(A1,-6)
with formatting resulting cell as date