Home

Excel Formulas

dguktous
New Contributor

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

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))
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 months back from the date is

=EDATE(A1,-6)

with formatting resulting cell as date

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies