Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default Calculate eligibility/enrollment date

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))


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Calculate eligibility/enrollment date

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

"PH@tic" wrote:

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))


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default Calculate eligibility/enrollment date

One way:

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


In article ,
PH@tic wrote:

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))

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default Calculate eligibility/enrollment date

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!

"JE McGimpsey" wrote:

One way:

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


In article ,
PH@tic wrote:

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))


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Calculate eligibility/enrollment date

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PH@tic" wrote:

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))




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Calculate eligibility/enrollment date

Hi,

If you want shorter then

=IF(D3,EOMONTH(D3,IF(EOMONTH(D3,2)+1-D390,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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PH@tic" wrote:

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))


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Calculate eligibility/enrollment date

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")

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PH@tic" wrote:

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))


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default Calculate eligibility/enrollment date

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.


In article ,
Shane Devenshire wrote:

If you want shorter then

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default Calculate eligibility/enrollment date

In which case, use 89 rather than 90.

In article ,
Shane Devenshire wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to calculate end date using order date and lead time variab BWA Excel Worksheet Functions 3 August 15th 08 04:48 PM
Calculating eligibility dates PH@tic Excel Worksheet Functions 4 July 7th 08 09:16 PM
Eligibility Dates Christy Excel Worksheet Functions 3 June 20th 08 09:22 PM
Statistics Plus Minus Enrollment [email protected] Excel Worksheet Functions 5 December 17th 06 02:25 AM
Auto calculate for date + days forward to yield new date John Sullivan Excel Worksheet Functions 1 April 22nd 06 05:18 PM


All times are GMT +1. The time now is 09:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"