• 460K Members
• 10.9K Online
• 557K Conversations

New Contributor

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

Re: Excel Formulas

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

Re: Excel Formulas

thanks for your response jimfikes, the formula is not working, its telling me i've entered too many arguments for this function.

Re: Excel Formulas

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

RE: Excel Formulas

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

Re: Excel Formulas

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