Review Date Calculation

[creamer]

Almost embarrassed to ask this question as I see the caliber of expertise in these forums but here goes...

I have a little database to keep track of 50 employees and I'm working on a layout to help me see when staff are due for reviews. I have a "start date" field and I'd like to create calculation fields based on the same. The calculations would tell me the date of a staff, 90 day, 6 month and annual reviews. 90 day is a one time event, 6 month and annual repeat for as long as they're here (I have an "end date" field too).

I would be grateful for any suggestions. Thank you.

[LiveOak]

This is actually a pretty tough one. For the 90 day date, it's simple:

Date90 (calculation, date) = Start Date + 90

The yearly date is not too bad:

AnnualDate (calculation, date) =

Case( not IsEmpty(EndDate) or IsEmpty(Start Date), TextToDate(""),

Date(

Month(Start Date),

If(Month(Start Date) = 2 and Day(Start Date) = 29 and not Mod(Year(Status(CurrentDate)), 4) = 0, 28, Day(Start Date)),

Case(

Month(Start Date) > Month(Status(CurrentDate)), Year(Status(CurrentDate)),

Month(Start Date) < Month(Status(CurrentDate)), Year(Status(CurrentDate)) + 1,

Month(Start Date) = Month(Status(CurrentDate)) and Day(Start Date) * Day(Status(CurrentDate)), Year(Status(CurrentDate)),

Year(Status(CurrentDate) + 1)

)

)

)

The six month date is more difficult, as it is subject to interpretation. Should the date by 182 days after the start date? Should the date by the same day of a month six months later? What happens if the start date is on the 31 of December and there is no 31st of June? What do you do if the start date is 2/29/2000? If we assume that something close is good enough:

SixMonts (calculation, Date) =

Case(Annual - Status(CurrentDate) > 183, Annual - 182, Annual)

-bd

[creamer]

Thanks so much for your time!

In the meantime I had gone down a similar, if simpler (perhaps not good) path. I used the following:

For the 90 day

If( Start Date + 90 <= Today, " ", Start Date + 90 )

For the 6 month

If( Start Date + 365/2<= Today, " " , Start Date + 365/2 )

For the annual

If( Start Date + 365 <= Today, " " , Start Date + 365 )

Then I just modified the 6 month and annual and created different fields for 1.5 year, 2 year, 2.5 year etc. Its not exactly what I want but it more or less works. I get a "?" in lieu of a blank entry if the date has already passed because the result is a calc. I changed it to a text result and got what I wanted but then changed it back because I want to create another calc field based on the result. I admit to not understanding your annual calculation in full, but I'll play with it and see if I can figure it out.

One thing I take from youre response it that naming of fields is important. I've got long names with spaces and I will adjust based on your suggestions - I assume that will be of help down the road as this thing gets more complex. Again, I appreciate your help.

-Nat-

[LiveOak]

To get rid of the "?" in the date field, use "TextToDate("")" instead of just "" to put a blank in a date field.

-bd