Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to calculate end date using order date and lead time variab | Excel Worksheet Functions | |||
Calculating eligibility dates | Excel Worksheet Functions | |||
Eligibility Dates | Excel Worksheet Functions | |||
Statistics Plus Minus Enrollment | Excel Worksheet Functions | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions |