How to Create Forward Dates?

[KATHWUN]

How to Create:
Need to have 6-mos forward date generated if certain criteria are met.
Need to have 12-mos forward date generated if criteria are not met.

APPLICATION:
---New Employee #1 starts on 02/03/02, is assigned to Radiology (high risk), and requires medical screening every six months (08/02/02, 02/02/03, etc.).
---New Employee #2 starts same day, assigned to Neurology (low risk), requires medical screening once per year (02/02/03, 02/02/04 etc.).

MODEL for Columns A, B, C, D:
Start ed-------Assigned to-------6-mos Med-------12-mos Med

The “high risk” Assigned to areas would be the criteria to be met to generate a 6-mos date. These are in Column B (“DEM”, “RAD”, “IMM”, “RT”, “PUL”, or “UCC”). Anything else in Col. B would = 12-mos forward date.

WHAT ABOUT
--When Start Date is for Old Employee: e.g., Started 02/03/99

[Mark W.]

Assuming that your data starts on the 2nd row... in C2 enter the formula...

=IF(OR($B2={"DEM","RAD","IMM","RT","PUL","UCC"}),EDATE(TEXT($A2,"m-d"),6),"")

...and fill down as needed. In D2 enter the formula...

=EDATE(TEXT($A2,"m-d"),12)

...and fill down as needed.

Note: The EDATE worksheet function is available after the installation of the Analysis ToolPak add-in.


[ This Message was edited by: Mark W. on 2002-05-15 18:11 ]

[PaddyD]

Does it matter whether the forward dates fall on 'workdays'? If not,
Assuming the data starts in row7:

=IF(OR(B7="DEM", B7="RAD",B7="IMM", B7="RT", B7="PUL", B7="UCC"),A7+180,A7+365)

If it does matter what day the forward date falls on, it will be a little harder.

Paddy


Or, having seen Mark's post, it wont be!

[ This Message was edited by: PaddyD on 2002-05-15 18:03 ]

[Yogi Anand]

On 2002-05-15 17:44, KATHWUN wrote:
How to Create:
Need to have 6-mos forward date generated if certain criteria are met.
Need to have 12-mos forward date generated if criteria are not met.

APPLICATION:
---New Employee #1 starts on 02/03/02, is assigned to Radiology (high risk), and requires medical screening every six months (08/02/02, 02/02/03, etc.).
---New Employee #2 starts same day, assigned to Neurology (low risk), requires medical screening once per year (02/02/03, 02/02/04 etc.).

MODEL for Columns A, B, C, D:
Start ed-------Assigned to-------6-mos Med-------12-mos Med

The “high risk” Assigned to areas would be the criteria to be met to generate a 6-mos date. These are in Column B (“DEM”, “RAD”, “IMM”, “RT”, “PUL”, or “UCC”). Anything else in Col. B would = 12-mos forward date.

If your StartDate ... entries begin in row 2, use the following formulas

in cell C2 ... =IF($B2={"DEM","RAD","IMM","RT","PUL","UCC"},EDATE($A2,6),""))

in cell D2 ... =IF($B2={"DEM","RAD","IMM","RT","PUL","UCC"},"",EDATE($A2,12))

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

[Yogi Anand]

Hi KATHWUN:

WHAT ABOUT
--When Start Date is for Old Employee: e.g., Started 02/03/99

for such a situation I came up with the following formulas -- so with StartDate of 02/03/99

=DATE(YEAR(NOW()),IF(MONTH($A6)

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!







[ This Message was edited by: Yogi Anand on 2002-05-15 18:38 ]

[KATHWUN]

TO: MarkW, PaddyD and YogiA
Mark, the Analysis ToolPak add-in must be missing, because your and Yogi's formulae do not work. Paddy, Yours DID work within the parameters I gave you.
And no, it doesn’t matter if the date is not a business-day date. We’ll compensate for that.

I STILL HAVE A PROBLEM:
EXAMPLE #1, say that I have an employee that started 02/03/98,and has had all med-screenings annually (99,00,01,02). Bouncing off of Start Date doesn't work here. It shows 3/3/99, but I need it to “think” 2/3/03.
EXAMPLE #2: a 7/9/01 start date requires 6-mos (180 days) med screening. Paddy’s formula generates 1/7/02. That suggests that he MISSED a screening. His ACTUAL next is scheduled for 7/9/02

QUESTION: Is there a way to make and ADD an assumption to the formula that says, in effect, “If the 6-mos (or 12-mos) incremental date generated is EARLIER than TODAY-date, calculate (count) the past increments from Start Date forward to next forward increment past TODAY date.
Here’s hoping. . . .


[ This Message was edited by: KATHWUN on 2002-05-15 19:32 ]

[PaddyD]

Haven't really thought this through, but what you need is some way to increment the number of times you calculate the 6 and 12 month periods depending on how many of them have passed since the start date. Something like:

=IF(OR(B7="DEM", B7="RAD",B7="IMM", B7="RT", B7="PUL", B7="UCC"),A7+(180*(YEAR(TODAY())-YEAR(A7))),A7+(365*(YEAR(TODAY())-YEAR(A7))))

might do it, but you'll need to check,

Paddy


Actually - this will only work for the yearly checks. You could just multiply the 180 day checks by 2, but you'd miss all the odd-numbered 6 monht periods.

If we add the logic to calculate the number of 6 month periods that have elapsed, the formula's going to get really messy (unless you do a 'six_months_elapsed' UDF). Might be better to calculate number of relevant periods that have elapsed, stick the results into new cells & use them to drive the calculations. (it might be usful to know how many checks each employee has had anyway)

paddy

[ This Message was edited by: PaddyD on 2002-05-15 19:59 ]

[Yogi Anand]

Hi KATHWUN:
To get the Analysis ToolPak working, all you have to do is

TOOLS|ADD-INs and then select Analysis_TolPak
in the dialog bos, the OK -- and you will have the functionality of Analysis ToolPak made available.

Regards!

[KATHWUN]

Paddy, I agree with counting the increments past. FEEDBACK: Using your latest formula, I put in hypothetical date of 2/3/99 and got date 7/27/00 -- or 9-mos. Oops.

QUESTION: Is excel reading (YEAR(TODAY())from the clock or do I need to reference a cell location to make this count increments?
______________________________________
=IF(OR(B7="DEM", B7="RAD",B7="IMM", B7="RT", B7="PUL", B7="UCC"), A7+(180*(YEAR(TODAY())-YEAR(A7))),A7+(365*(YEAR(TODAY())-YEAR(A7))))

I'll check in tomorrow a.m.
Thanks, Paddy.
Kath

[PaddyD]

Kath,

If you don't mind adding extra fields to your table, I'd go with:

StartDate : Category ('DEM' etc): Period Type (6 or 12 month): Periods Since Start : Next Review

With the following codes:

Period Type:
=IF(OR(B2="DEM", B2="RAD",B2="IMM", B2="RT", B2="PUL", B2="UCC"),6,12)

Periods Since Start:
=IF(C2=12,YEAR(TODAY())-YEAR(A2),IF(C2=6,ROUND((TODAY()-A2)/180,0),""))

Next Review:
=IF(C2=6,A2+(180*D2),IF(AND(C2=12,D2<>0),(A2+(365*(D2+1))),(A2+365)))


The only anomaly I can find is that those on a six month review frequency with a start date after today get a review on their start date.

Will this do?

Paddy


P.S. Today() runs off the clock


[ This Message was edited by: PaddyD on 2002-05-15 21:47 ]