Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here


subtracting dates in approach

subtracting dates in approach

subtracting dates in approach

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

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close