Another Date Calculation Question

[mginipro]

I'm trying to set up date calculations for our mailings. I've looked at the other posts on date calculations and didn't see a solution for what I'd like to create.

I want to set up our database to give me a found set every Monday (when our mailings go out). Here are the fields I am working with:

InitialDate: when we first hear from someone

Letter1Date, Letter2Date, etc.: the dates when we send our weekly mailings, which I want to be on Mondays

Saying "Letter1Date=InitialDate+7", "Letter2Date=InitialDate +14" and having it formatted as a date gives me one week (and two weeks) out, that I figured out. What I want to have happen is to have that date then get moved to the Monday of those weeks, I just don't know what the calc for that would be.

Example: InitialDate is Wednesday 4/23/2003, currently meaning that Letter1Date would come back as 4/30/2003 (next Wednesday). What would I need to add to the calculation to have it return 4/28/2003 (the Monday of that week) instead?

Thanks!

[danjacoby]

Use the "Case" function:

Case(DayName{InitialDate) = "Monday", InitialDate + 7,

DayName{InitialDate) = "Tuesday", InitialDate + 6,

etc.

[Ugo DI LUCA]

Hi,

This Monday = Initial Date - MOD(DaysOfWeek(Initial Date) +5; 7)

Next Monday = Initial Date + MOD(9- DaysOfWeek(Initial Date)+7; 7)

You'll notice that if Initial Date is already a Monday, both "This Monday" and "Next Monday" will return the same result, so even if Next 2Mondays should be :

Initial Date + MOD(9- DaysOfWeek(Initial Date)+14; 14),

as Dan, I'll prefer having a Case statement :

Next Monday = Case(DayName(Initial Date) = Monday, Next Monday + 14, Next Monday +7)

[mginipro]

Thanks Dan & Ugo!

Letter1Date=Case(DayName(Introdate)="Monday", Introdate+7,

DayName(Introdate)="Tuesday", Introdate+6,

DayName(Introdate)="Wednesday", Introdate+5,

DayName(Introdate)="Thursday", Introdate+4,

DayName(Introdate)="Friday", Introdate+3)

Then I can simply have Letter(2,3,...)Date be Letter1Date+(7,14,etc)

This works perfectly for what I want to do, however I need to throw

one wrench into it. If the introdate is prior to September 1 of that

year, I need the result of Letter1Date to be "9/1/2003".

Example: I work in college athletics and we are not allowed to send

out certain mailings until Sept. 1 of a recruits junior year in HS.

So, if I mailed a questionnaire on 4/12/2003 to a sophomore, I need

Letter1Date to be "9/1/2003" not "4/15/2003", which is what the above

calculation would give me.

I've tried adding to the CASE statement, but can't get anything to

give me the correct date.

[Ugo DI LUCA]

Hi,

Try this

Letter1Date=

Case(Introdate > Date(Year(introdate),9,1),

Case(DayName(Introdate)="Monday", Introdate+7,

DayName(Introdate)="Tuesday", Introdate+6,

DayName(Introdate)="Wednesday", Introdate+5,

DayName(Introdate)="Thursday", Introdate+4,

DayName(Introdate)="Friday", Introdate+3),

FirstMonday of September )

[Ugo DI LUCA]

...what about using this formula for the first moday of september :

Next Monday of september = Date(Year(initial Date), 9,1) + MOD(9- DaysOfWeek(Date(Year(initial Date), 9,1))+7; 7)

I didn't checked it though...

[Ugo DI LUCA]

Hi again,

I'm a bit lazy, but here's the formula I tested

French though : substitute TEST with Case, " ; " with ", " and reverse the dates for your US Format

ANNEE is Year

Lundi is "Monday"

Nom Jour is DayName...

It's getting hard to work with you US

TEST(initial date > DATE(9; 9; ANNEE(initial date));

TEST(NOMJOUR(initial date)= "Lundi"; next moday + 14; next moday +7);

DATE(9; 9; ANNEE(initial date)) + MOD(9- JOURSEMAINE(DATE(ANNEE(initial date); 9;1))+7; 7))

[Lee Smith]

Hi Ugo,

You make me laugh.

[color:"blue"]> It's getting hard to work with you US

Wouldn't it have been easier for you to just make the changes. I think you spent more time on explaining what to change than it would have taken to just make the changes.

[Ugo DI LUCA]

Hi Lee,

Yo're right. I realized it when typing the instructions

[mginipro]

Case(Introdate > Date(9,1,2003), Case(DayName(Introdate)="Monday", Introdate+7,

DayName(Introdate)="Tuesday", Introdate+6, DayName(Introdate)="Wednesday", Introdate+5,

DayName(Introdate)="Thursday", Introdate+4, DayName(Introdate)="Friday", Introdate+3), Date(9,1,2003) )

This finally did it. I appreciate all the help!

Matt

[Ugo DI LUCA]

hehe,

you will have to change the calc every year !!

[Lee Smith]

[color:"blue"] > you will have to change the calc every year

To add flexibility to this calculation, add a field called School Year, text, (type in the year such as 2003), and then modify your calculation to:

Case(Introdate > Date(9,1,School Year), Case(DayName(Introdate)="Monday", Introdate+7,

DayName(Introdate)="Tuesday", Introdate+6, DayName(Introdate)="Wednesday", Introdate+5,

DayName(Introdate)="Thursday", Introdate+4, DayName(Introdate)="Friday", Introdate+3), Date(9,1,School Year) )

HTH

Lee

[Ugo DI LUCA]

Hi Lee,

As usual, ******* right as my first suggestion to base the year based on the Initial date could have been inaccurate in some circumstances (making it in december, would have brought the letter in september from that date.)

Thanks Lee for your clear reading.

[mginipro]

I'm glad I can provide a forum for you to go back and forth with each other! Thanks to both of you for helping me figure this out.

I did notice that I would have to change the year each time, but that actually works for me. To make it easier for us NCAA-wise, we have separate DB's for each graduating year. So, I just create clones and will change the year accordingly.

[RussBaker]

Just remember that when you create a clone, all your global fields will be cleared...