Date Calculation Help!

[Calantha]

Hello,

I am trying to do a date calculation which requires the date result not to be a weekend.

I work at a school and our database contains all the dates of contact between us and the students (ie. 07/08/2005 Sent Info Package). I would like to have a field which automatically shows me the next contact date. The next contact date is based on the reason for the last contact. So far I have this working perfectly (i.e. if the last reason for contact was an info package the next contact date is three weeks after).

Where I am unsure is I do not want the Next Contact Date to be either a Saturday or Sunday... So... something like "if the last reason for contact was an info package the next contact date is three weeks after but if that date is a saturday or sunday add 2 days"...

Is this possible?

[comment]

Try:

nextDate + Choose ( Mod ( nextDate + 1 , 7 ) , 2 , 1 )

[Calantha]

Thanks for the reply... However I am a little confused by that. This calculation is taking place in the Next Contact Date field, so when I try an add to Next Contact Date within the calculation box it gives me an error saying it would be a circular calculation, which I understand. So where would I put that calculation?

As well... How does it know if the date has been a Saturday or Sunday?

Thank You,

Calantha

[comment]

I can't say precisely how you should incorporate this, because I don't see what you have now.

If, for example, your current formula were:

Next Contact Date =

someDate + 21

you would change it into:

someDate + 21 + Choose ( Mod ( someDate + 22 , 7 ) , 2 , 1 )

[Calantha]

Hello, I'm starting to understand by I am still having trouble understanding how it is changing the date based on whether the date is a weekend.

This is an example of what I have in the Next Contact Date field:

Case(Last(Reason For Contact)="Info Package", Last(Contact Date)+21)

[Calantha]

Ok... I stopped asking and just put the equation you suggest in and it works like a charm!!

I looked up the Choose and Mod functions but my math not being top-notch I'm not quite sure how it works.

If you wouldn't mind providing me a brief explanation I would be more than thankful, but only if you have some extra time. Otherwise I am extremely grateful for the equation that works lovely!

Thank you!

[Calantha]

Actually, it isn't necessary for you to explain, I got our Math teacher to do it for me. Thank you again!

[comment]

Ah, good. Having a math teacher on call is very handy - I often wish I had one, too...

[-Queue-]

My degree was in Applied Math. Is that sufficient? B)

[comment]

But are you on call?

[-Queue-]

Well, I do have a pager, and you can always email me if I don't respond immediately. B)

[comment]

It's not the same thing. But I'll get back to you on this if and when I get Skype. Then you'll be sorry you've made this offer...

[-Queue-]

Bring it!