subtracting dates in approach

[jeffp60]

i need to keep track of 6 dates for 9 months and would like to either automaticly clear the oldest one or announce that it is outdated or that there is 6 or more dates. so, i would enter 1/1/07,1/5,/1/7,3/5,5/6,5/8...i would like to count 9 months back from today,count the dates,clear the ones that are older than 9 months.
how do i:
count the months between two dates-9months
count the dates-6 dates
clear old dates- older than 9 months

 

[suesloan]

I will assume you have 6 dates within one record and they are named D1 thru D6. Further that they are all in the past and not in the future vs today.

1)Months between two dates - The main problem here is considering the change in years. Month(datefield) will give you the month number, Year(datefield) would give you the year number. So, using D1 as an example, this calculated field would work even if the D1 field is blank:

NumMonthsD1 = If(Year(Today()) = Year(D1),Month(Today()) - Month(D1),Month(Today() + 12 - Month(D1))

The above ignores the day in the month, but you could add a test or adjustment by adding another calculated field:

MonthAdjustedD1 = If(NumMonths = 9 and Day(Today()) <= Day(D1),NumMonths -1,NumMonths)

2) Counting the months that are filled in would use the IsBlank() function:

Filled = If(IsBlank(D1),0,1) + If(IsBlank(D2),0,1) etc..

3) Clearing the dates older than 9 months would simply require you to compute the MonthAdjustedxx for the 6 fields and then you could use a macro to SET the older fields blank with something like this one for D1:

SET D1 = If(MonthAdjustedD1 >= 9,'',D1)

An alternative might be to set up "modification formulas" in Field Definition for the date fields that would clear them automatically when any field in the record is updated. The formula would be the same as the above macro If condition.

Sue Sloan

 

[jeffp60]

thanks, i figured out a way....long way but it works, i made 4 set for each entry.....
If("OCCASION 3" = "OCCASION 2", ' ', "OCCASION 3")
If(IsBlank("OCCASION 3"), "OCCASION 4", "OCCASION 3")
If("OCCASION 3" = "OCCASION 4", "OCCASION 3", "OCCASION 3")
If(IsBlank("OCCASION 3"), "OCCASION 4", "OCCASION 3")