Benefits Eligibility

[ynikolic]

I am looking for a formula that will allow me to have a date that is 1st of the quarter after 180 days of employment.

So, for instance in first column is hire date, in second column is the date that will be 1st of quarter after 180 days employment.

Date of Hire 3/16/2009
1st of quarter date would read: 10/01/2009

 

[texasalynn]

Welcome to Mr Excel Board...

I setup an area with the quarter dates, then you can use a vlookup

try this

Excel Workbook
	A	B	C	D
1	Date of Hire	3/16/2009	*	1/1/2009
2	1st of Quarter date	10/1/2009	*	4/1/2009
3	*	*	*	7/1/2009
4	*	*	*	10/1/2009
Sheet3

 

[facethegod]

Try..

=DATE(YEAR(A1+180)+(MONTH(A1+180)>=10),MOD((CEILING(MONTH(A1+180)/3,1))*3+1,12),1)

 

[ynikolic]

Thank you both so much. Both work wonderfully, and you have saved me a lot of time! Thanks again.