Formula to calculate benefit date eligibility to next quarter

[RicardoCubed]

Looking for a formula to calculate an employees benefit eligibility date once employed. eligibility dates are on a quarterly basis so Jan 1, Apr 1, Jul 1 and Oct 1 are the dates. So if employee starts on 2-1-14, eligibility date would be 4-1-14. Needs to work for 12-31-13 employment date = eligibility date would be 01-01-14. If employee starts on 1-1-14, eligibility date 1-1-14.

Employee Hire Date Eligibility Date
Jonny Jones 2/1/2014 4/1/2014
Ricardo Cubed 5/30/2012 7/1/2012
Harry David 9/15/2014 10/1/2014
Susie Que 12/31/2013 1/1/2014
Newbie Too 1/1/2014 1/1/2014

thanks for any help you can provide!!!

 

[Marcelo Branco]

What are the expected results if employee starts at
4/1/2014 = ?
7/1/2014 = ?
10/1/2014 = ?

M.

 

[vogel997]

Array Start Date Eligibility date
10/2/2011 1/1/2012 2/1/2014 4/1/2014 =VLOOKUP(D2,$A$2:$B$18,2)
1/2/2012 4/1/2012 5/30/2012 7/1/2012 =VLOOKUP(D3,$A$2:$B$18,2)
4/2/2012 7/1/2012 9/15/2014 10/1/2014 =VLOOKUP(D4,$A$2:$B$18,2)
7/2/2012 10/1/2012 12/31/2013 1/1/2014 =VLOOKUP(D5,$A$2:$B$18,2)
10/2/2012 1/1/2013 1/1/2014 1/1/2014 =VLOOKUP(D6,$A$2:$B$18,2)
1/2/2013 4/1/2013
4/2/2013 7/1/2013 Try this.
7/2/2013 10/1/2013
10/2/2013 1/1/2014
1/2/2014 4/1/2014
4/2/2014 7/1/2014
7/2/2014 10/1/2014
10/2/2014 1/1/2015
1/2/2015 4/1/2015
4/2/2015 7/1/2015
7/2/2015 10/1/2015
10/2/2015 1/1/2016

 

[barry houdini]

If hire date is in B2 then try this formula in C2 for the eligibility date

=COUPNCD(B2-1,DATE(2999,1,1),4,1)

format C2 in required date format

 

[RicardoCubed]

Barry - so elegant and awesome. thanks for sharing the magic!!!!

 

[jamilm]

If hire date is in B2 then try this formula in C2 for the eligibility date =COUPNCD(B2-1,DATE(2999,1,1),4,1) format C2 in required date format

This is truly extra ordinary