×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# subtracting dates in approach

## subtracting dates in approach

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

### RE: subtracting dates in approach

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

### RE: subtracting dates in approach

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!