Excel Tricky Date Question
I have a date in cell A1.
It's the hire date of one of my employees: 9/12/2014.
Her paid vacation days -- if not used -- always expire 364 days later.
Today is 9/17/19.
How do I write a formula (not an array) to return the expiration date -- and for every future year as the year changes -- in my spreadsheet?
For example, in today's case the result cell A2 should return the date 09/11/2020.
In a few months on January 1, 2020, I would still want the expiration date to remain 09/11/2020.
On 9/12/2020, I would want the expiration date to change and then be 09/11/2021.
Hope this makes sense, and thanks in advance!
It's the hire date of one of my employees: 9/12/2014.
Her paid vacation days -- if not used -- always expire 364 days later.
Today is 9/17/19.
How do I write a formula (not an array) to return the expiration date -- and for every future year as the year changes -- in my spreadsheet?
For example, in today's case the result cell A2 should return the date 09/11/2020.
In a few months on January 1, 2020, I would still want the expiration date to remain 09/11/2020.
On 9/12/2020, I would want the expiration date to change and then be 09/11/2021.
Hope this makes sense, and thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks! Glad I could help!
Slight tweak (shown in Bold) needed as current formula gives 09/12/2020 rather than the 09/11/2020 as required:
=IF(TODAY()>=DATE(YEAR(TOD
=IF(TODAY()>=DATE(YEAR(TOD
@Rob - Good catch! That's the problem with submitting late night solutions!
@Cactus - As Rob stated, his solution will give you the 11th and not the 12th.
Paul
@Cactus - As Rob stated, his solution will give you the 11th and not the 12th.
Paul
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sweet!
ASKER
Wow. Rob, this is even better. Thank you so much.
We have a lot of employees with obviously different hire/anniversary dates, varying numbers of vacation days, and this helps tremendously ... and not only just with vacation days, but other areas I can use the formula with.
Thank you both so much! Tim.
We have a lot of employees with obviously different hire/anniversary dates, varying numbers of vacation days, and this helps tremendously ... and not only just with vacation days, but other areas I can use the formula with.
Thank you both so much! Tim.
ASKER
Hmm. I jumped the gun. This last formula, Rob, doesn't work when I enter the date: 01/29/19.
The result is 01/29/20, not 01/28/20.
Thoughts?
Example.xlsx
The result is 01/29/20, not 01/28/20.
Thoughts?
Example.xlsx
Tried my suggestion with a whole string of dates (a couple of years worth) with varying results, I'm sure there would be some kind of theme that could be identified to enable adjusting the formula but that would just add in various IF statements making it overly complicated.
Sounds like it is better to go with Flyster's suggestion. I have made one more tweak to his though:
=DATE(YEAR(TODAY())+IF(TOD
Still doing basically the same but his original formula was longer than it needed to be. Original was
IF Today's date is beyond Hire Date THEN Create Date with
TODAY'S YEAR+1, Month of Hire, Day of Hire -1
OTHERWISE Create Date with
TODAY's Year, Month of Hire, Day of Hire -1
As you can see the only difference with the two formulas in the IF statement is the YEAR + 1.
I have rearranged to:
Create Date with TODAY's Year + 1 IF needed, Month of Hire, Day of Hire -1
Sounds like it is better to go with Flyster's suggestion. I have made one more tweak to his though:
=DATE(YEAR(TODAY())+IF(TOD
Still doing basically the same but his original formula was longer than it needed to be. Original was
IF Today's date is beyond Hire Date THEN Create Date with
TODAY'S YEAR+1, Month of Hire, Day of Hire -1
OTHERWISE Create Date with
TODAY's Year, Month of Hire, Day of Hire -1
As you can see the only difference with the two formulas in the IF statement is the YEAR + 1.
I have rearranged to:
Create Date with TODAY's Year + 1 IF needed, Month of Hire, Day of Hire -1
ASKER
Great. Thank you for the description, and the revised formula (reverting back to Flyster, but with modification.) This is all very much appreciated, and thank you both so much. I'm running a larger spreadsheet for the company, and this was an "automated" calculation I need to have -- and not just for vacation day anniversary dates.
Have a great night, and thanks again!
Have a great night, and thanks again!
ASKER
Thank you!