Using hire date to convert either a year later or the first of the year based on the hire date

[BKrantz]

Hello,
I am trying to do a vacation accrual worksheet base off of a start date, or the first of the year. So I need the cell to populate either the hire date or 1/1/18 if they have already been employed for one year.

So in Cell B5 I have the hire date in cell C5 I have the termination date (or a date in the far future) in cell D5 I need it to populate either the hire date or 1/1/18, but if they are terminated I need it to post the termination date.

Can anyone help with this formula?

 

[Joe4]

Welcome to the Board!

Maybe something like this:

=IF(C5,C5,MAX(B5,DATE(2018,1,1)))

 

[jtakw]

Hi,

If I understand correctly, does this do what I think you described:

Book1
	B	C	D
4	Hire Date	Termination Date	Result
5	7/4/2016	12/25/2019	1/1/2018
6	10/15/2017	12/25/2018	10/15/2017
7	2/10/2017	9/21/2018	9/21/2018
Sheet285

Cell Formulas
Range	Formula
D5	=IF(C5<=TODAY(),C5,IF(EDATE(B5,12)>=TODAY(),B5,DATE(YEAR(TODAY()),1,1)))

 

[Joe4]

The way that I read it was like this:
Put in cell D5:
- The termination date (C5), if one exists
- Otherwise, the later of the Hire Date and 1/1/2018

 

[BKrantz]

Both of these do work. Maybe I just need to elaborate on what exactly I am trying to accomplish.
I have my employees and they earn 120 hours of vacation per year with 1/1 date/renewal for all employees. So if someone starts mid year I need to pro rate the total, and if someone has been here for more than a year I need 1/1 to be their date. Our vacation rolls over fully every year if un-used. Also if they are terminated I need some how this to also stop accruing because I will populate the full balance in used as we will pay out all un-used time. (((( this sheet flows into our budget sheet to show liability per employee))))

My thoughts were as follows:
Headers:
A1 Name
B1 Hire Date
C1 Termination Date
D1 Current Year Accrual date
E1 Carry Over Previous Years
F1 Vacation Used
G1 Vacation Balance (As this will be the total I used in the budget sheet for liability)

Hope this somewhat makes sense

 

[jtakw]

@Joe4, OP's description is unclear to me, my suggested solution is just my guess at what OP wanted...

 

[BKrantz]

I'm also completely open to other suggestion how to accomplish this.... Thanks in advance for the help!!!