I'm trying to do a search on a date range, but it needs to be flexible. I have a list of end dates, and each week I would like to be able to run a script that returns projects that are due to end in the next week.

I wouldn't have thought it would be too dificult, but I just CANNOT make it happen, with a find or script or anything!

Help!

Thanks

Sam

Dates can be difficult to do finds and sorts on if they are not formatted properly. I deal with this by formatting dates yymmdd in a calculation field and then do all finds and sorts on this field. If your dates are formatted mm/dd/yy, your calc would be:

right(datefield,2)&

left(datefield,2)&

middle(datefield,4,2)

07/05/01 becomes 010705.

If months are expressed in text, June instead of 06 or 6, you'll have to convert the month to a number. The best way to do this is with a case statement.

To search the range of dates bewteen June 1 and July 5 search for:

010601...010705.

Note that dates prior to the 01/01/00 will sort/find after dates in the new millenium.

Good luck.

[ July 05, 2001: Message edited by: esteshk ]

Brilliant, thankyou!

One question - can I search for a weeks time, but leave it flexible? in other words, I want to look for today + 7. Is this possible?

Thanks

Sam

Sometimes we just don't answer the question you asked! .

To setup the find in a script:

Go to Layout[All fields] <--- field must be visible on current layout

Enter Find Mode[]

Insert Calculated Result[YourDateField,Status(CurrentDate)]

Insert Text["..."]

Insert Calculated Result[YourDateField,Status(CurrentDate) + 7]

Perform Find[]

-bd

And sometimes we try to give folks some insight into different ways of doing things. Status(CurrentDate) works well if you are starting from today! I thought it might be beneficial to Sam to have a general method for searching dates regardless of the start and end dates.

Joking aside, any and all help answering questions is GREATLY appreciated!

-bd