Approach User Support homepage Approach User Support
Answers to Frequently Asked Questions about Lotus Approach
Examples LotusScripts
Example databases
Links to other Lotus Approach support services

[Return to contents]

Finding or calculating dates

Last updated: 4 Feb 2002

First, make sure your date is in a date field and not a text field. Trying to work with date in text fields is inviting all sorts of problems - that why they invented date fields! You can extract a date from a text field (eg 09/11/2001 or 09-11-2001) using the TextToDate() function. If this doesn't work then try creating your own custom function such as:


(However, TextToDate() will do that on correctly, but you get the idea...)

OK, once you have your dates in a date field, use the following to find what you are looking for:

Put the range (eg 9/1/1998...9/30/1998) into the desired field in FIND.

Try using or adapting the following formulas:

   For NOT December: IF(month(date_field)<>12)
   For this month: IF(month(today())=month(date_field))
   For December 1996: IF(month(date_field)<>12 AND year(date_field)=1996)
   For the last 90 days: <=@TODAY()&>=@TODAY()-90
   Between two dates: If(DB.Date >= SDate and DB.Date <= EDate)
                  or: <@end_date & >@begin_date
   3 business days from now: If(DayOfWeek(Date + 3) = 7 or DayOfWeek(Date
                             + 3) = 7, Date + 5, Date + 3)
   One year from a date: If(Month("Date Joined") = 2 and Day("Date Joined") = 29, Combine(Month("Date Joined"), '/', Day("Date Joined") - 1, '/', Year("Date Joined") +1),Combine(Month("Date Joined"),'/',Day("Date
Joined"),'/',Year("Date Joined") +1)).

   NOTE: This last calculation assumes that if the Month is 02 and day is 29, then the date a year will be 28.

In v96 (and following), To find "This Month" use the Find Assistant and opt for "This Month".

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.