I have an active direct marketing campaign where I feed a new name in one side (at various dates) and the proceed through a series of mailings until they become a client or are flushed out the back.

I note the date of the first mailing (FirstMail) to them and date of last mailing (LastMail).

I want to auto calculate the next Monday two weeks forward as (NextMail). If I happen to send out something on say Wednesday it can be slightly shorter than two weeks but I want my DB to tell me each Monday when I check who needs to mailed to this week.

I've tried playing with various ideas and read some boards but nothing is what I really need.

Any help would be appreciated.

What date do Sundays return? IOW, if you take Sunday, May 4, 2008, do you want the 12th or the 19th?

If you want the Sunday to be the 19th then use:

DateSent - DayOfWeek ( DateSent ) + 16

If you want the Sunday to be the 12th then use:

DateSent - DayOfWeek ( DateSent - 1 ) + 15

The rest of the days should be fine.

* error trap for empty and invalid entered dates.

Edited May 9, 200817 yr by Guest
added ps

I'd start by modifying this custom function, which returns the next monday from any date:

NextMonday

You need FM Advanced for defining Custom Functions, but perhaps studying this formula will help you define the correct calc for Next Mailing which doesn't need to be a custom function.