Calculate eligibility/enrollment date

[PH@tic]

We've changed our rules for calculating eligibility date for certain benefits
from 1 year of service to 90 days of service. Yet even after 90 days,
enrollment cannot be until Jan 1, Apr 1, Jul 1, Oct 1 of any year. How can I
adjust the following formula to calculate 90 days plus the time to the next
interval above to arrive at an appropriate eligibility/enrollment date?

=IF(ISBLANK(D3),"not yet",DATE(YEAR(D3)+1,INT((MONTH(D3)+2)/3)*3+1,1))

 

[Mike H]

Hi,

Maybe this

=IF(ISBLANK(D3),"not
yet",IF(DATE(YEAR(D3),MONTH(D3)+3-MOD(MONTH(D3)-1,3),1)>D3+90,DATE(YEAR(D3),MONTH(D3)+3-MOD(MONTH(D3)-1,3),1),DATE(YEAR(D3),MONTH(D3)+6-MOD(MONTH(D3)-1,3),1)))

All in 1 line

Mike

 

[JE McGimpsey]

One way:

=IF(LEN(TRIM(D3))=0,"not yet",
DATE(YEAR(D3+90),INT((MONTH(D3+90)-1)/3)*3+4,1))

 

[PH@tic]

I've tried Mike H's suggestion. It works but cumberson to re-type. I'll try
this shorter suggestion. Also spend some time to analayze the functions so I
understand "why" they work. THANKS!

 

[Shane Devenshire]

Hi,

Just a little warning, if you are elegible at 90 days not after 90 days then
you had better revisit JE's formula for a date link January 2, 2008. It will
return 7/1/08 when it should return 4/1/08.

 

[Shane Devenshire]

Hi,

If you want shorter then

=IF(D3,EOMONTH(D3,IF(EOMONTH(D3,2)+1-D3>90,2,5))+1,"Not yet")

1. With regard to my previous response you may need to change the >90 to

=90 in the above formula.

2. If you are using 2007 enter the formula as above
3. If you are using an earlier version of Excel you need to attach the
Analysis ToolPak by choosing Tools, Add-ins and checking Analysis ToolPak.

 

[Shane Devenshire]

Hi,

Please disregard the last solution, it was not tested for all dates, I will
try to modify it as needed.

In the mean time, shorten the formula as

=IF(D3,DATE(YEAR(D3+90),INT((MONTH(D3+90)-1)/3)*3+4,1),"not yet")

 

[JE McGimpsey]

Shorter, but not correct...

Further, EOMONTH, at least with the ATP, will be far slower than using
the built-in XL functions (and using 3 references rather than 2 will
slow it further).

Also,


=IF(D3,...

is equivalent to

=IF(D3=0,...

so if users "clear" a cell by hitting the space bar (something my
clients do all the time), the formula will return:

#VALUE!

Better:

=IF(LEN(TRIM(D3))=0,...

or in this case:

=IF(ISNUMBER(D3),...

etc.

 

[JE McGimpsey]

In which case, use 89 rather than 90.