I've attached a sample spreadsheet and I'm trying to find a formula for column E, which is based on the employee's hire date I need to know the date when their vacation days are due.
I've attached a sample spreadsheet and I'm trying to find a formula for column E, which is based on the employee's hire date I need to know the date when their vacation days are due.
Hey tastjuste, try pasting the below formula into cell E3 and dragging down:
Formula:Please Login or Register to view this content.
The logic of the formula is this:
If the employee's hire anniversary hasn't happened yet this year, then their days are due on their anniversary this year.
Otherwise, their days are due on their anniversary next year.
Have a great day!
Try this, copied down...
=DATE(YEAR($J$1),MONTH(B3),DAY(B3))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much it works!!!!!
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)
You could also use this formula to get the next anniversary date
=EDATE(B3,DATEDIF(B3,J$1,"Y")*12+12)
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks