Excel Formulas

[dguktous]

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.

[#2]

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))

[#3]

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!

[#4]

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))

[#5]

Sorry about that. I left off a closing parenthesis after the first A1. It should read: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

[#6]

6 months back from the date is

=EDATE(A1,-6)

with formatting resulting cell as date