Date Calculations....

[Craig Stewart]

I want to make a field that automatically enters a date that is a specified number of days after the date entered in a different field, but it can not return a result that is a weekend either. I have no clue how to do this and I am hoping some nice person can tell me...

Cheers

Craig Stewart

[-Queue-]

Check out the DayOfWeek() function. It will return 1 for Sunday and 7 for Saturday.

So create a date calculation field datecalc with the contents: Case( DayofWeek(Date1 + number) = 1, Date1 + number + 1, DayofWeek(Date1 + number) = 7, Date1 + number + 2, Date1 + number).

This will test on what day of the week Date1 + number falls. If it is a Sunday, then it will add one more day to make it Monday. If it is a Saturday, it will add 2 days to make it Monday. (If you prefer dates falling on Saturday to display Friday instead, then use "Date + number - 1" instead of "Date + number + 2".) If neither is true, then it will just return Date1 + number.

Will that work for you?

[Craig Stewart]

Thank you VERY much!!! I have fiddled for ages ( I am talking years) trying to work this out, coming back to it every so often. Now in seconds I have the answer. Must take a Filemaker course.

I have thought of something that didn't occur to me previously because I had no working version of this calculation. Can it be modified so that if another field has a yes or no value, then it brings a text result of "don't call" (perhaps the trext result would display in another field? as I know that these are Date fields at present) I am trying to set up a way that automatically tells me what customers I need to call and when, and also if I have reached them already, that I don't need to call again.

But thanks again for your help so far, I wish I could repay you!

[-Queue-]

Sure thing.

Case( yes_no = "no", "Don't call", Case( DayofWeek(date1 + number) = 1, DateToText( date1 + number + 1 ), DayofWeek(date1 + number) = 7, DateToText( date1 + number + 2 ), DateToText( date1 + number ) ) )

Set the calculation result as Text, and you're gold.

I wish I could repay you!

The feeling is mutual, friend.

[Craig Stewart]

Excellent! Thanks again. I really must take a course on Filemaker. the Filemaker Bible that I have tells you all about the application but not how to do stuff like this, which I assume is very (very) basic.

Thanks again

Craig Stewart

[J.P.]

Craig Stewart said:

Thank you VERY much!!! I have fiddled for ages ( I am talking years) trying to work this out, coming back to it every so often. Now in seconds I have the answer. Must take a Filemaker course.

I have thought of something that didn't occur to me previously because I had no working version of this calculation. Can it be modified so that if another field has a yes or no value, then it brings a text result of "don't call" (perhaps the trext result would display in another field? as I know that these are Date fields at present) I am trying to set up a way that automatically tells me what customers I need to call and when, and also if I have reached them already, that I don't need to call again.

But thanks again for your help so far, I wish I could repay you!

Version: v5.x

Platform: Windows XP