I've perused Brian Dunning's custom functions but can't seem to find what I'm looking for.

I'm attempting to find a custom function that will use two different dates -- DateSent and DateReceived -- to then calculate if DateReceived was, say, the first Monday after DateSent.

In other words: if the DateSent is 9/7/2010 and the DateReceived is 9/15/2010, I'd like the DateReceived to also be shown as the second Wednesday after DateSent. Make sense?

Ultimately this information will be [hopefully] used as plot points on a line graph where the horizontal axis will mark the 1st M,T,W,Th,F, 2nd M,T,W,Th,F, and so on.

Edited September 7, 201015 yr by Guest

... but 9/15/10 is the second Wednesday after 9/7/10.

What are the arguments to your function? DateSent, DateReceived, and what else, (day of the week, number of weeks?). Do you want it to return a single numerical value (e.g. number of weeks) or a boolean (True / False)?

In other words: if the DateSent is 9/7/2010 and the DateReceived is 9/15/2010, I'd like the DateReceived to also be shown as the first Wednesday after DateSent.

Do you mean the Second Wednesday after DateSent? The first Wednesday after 9/7/10 is 9/8/10.

Let([

d1 = Table::DateSent;

d2 = Table::DateReceived;

days = d2 - d1 -1;

ord = 1 + Div(days;7);

result = Case(Ord>0; Ord & " " & DayName(d2))

];

result

)

... but 9/15/10 is the second Wednesday after 9/7/10.

Heh, yeah.

What are the arguments to your function? DateSent, DateReceived, and what else, (day of the week, number of weeks?). Do you want it to return a single numerical value (e.g. number of weeks) or a boolean (True / False)?

DateSent and DateReceived are tied to a PackageCode. For now we're only going to be measuring within the first five weeks of DateSent. So, if DateSent is 8/30/2010, we'd be reporting on DateReceived five weeks forward. I don't have any number of weeks, day of week, etc fields set-up -- the dates are strict mm/dd/yyyy.

Ideally the function would return a single value -- boolean might work also ("true" when DateReceived = 3rd Monday after DateSent, or something to that effect).

I've attached a sample graph to hopefully help illustrate the ultimate goal. The x-axis shows the five weeks after DateSent broken down by day, the y-axis would show how many records were returned on that day.

visual_test.pdf

Yeah, I went back and edited the initial post.

I'll give this a shot and let you know how it goes. Thanks!

Fantastic -- this worked! Thanks a lot!