How to calculate the date of the next Wednesday?

Any suggestions out there?

this is a way

next_Wednesday.zip

WOW. Thanks a lot. My billing is done Wednesday-ily. I don't fully understand this but I do know enough to incorporate this into my DB.

Assuming you mean next (upcoming) Wednesday, not necessarily Wednesday of next week, you could try:

Get(CurrentDate) + DayOfWeek ( 10 - DayofWeek ( Get(CurrentDate) ) )

Edited October 12, 200817 yr by Guest
Fixed a typo

Get(CurrentDate) + DayOfWeek ( 10 - DayofWeek ( Get(CurrentDate) ) ) )

I tried that in calc field but when I click to create it after pasting it, I get an error, "An operator is expected here." This seems to be simpler than the other solution above but I fail to see why there is an error here.

Sorry, an extra parenthesis slipped in during copy/pasting. Fixed now.

Get(CurrentDate) + DayOfWeek ( 10 - DayofWeek ( Get(CurrentDate) ) )

Nice one !

The fixed number part is always the day of week number to search + 6, so it can go from 7 ( Sunday ) to 13 ( Saturday ).

My calc contains even an error due to my poor English ( I thought that Wednesday was the Italian Giovedì ): fixed

Actually, it needs to start at 8 as the minimum, or it will fail on a Saturday.

I see. I would never have guessed that days of the week could be 8+..... Filemaker just really surprises me sometimes.. Thanks.

Actually, it needs to start at 8 as the minimum, or it will fail on a Saturday.

I think that THIS time you are wrong...

7 gives always Sunday

BTW: the number can go from 3 to 9 ( or those values + multiple of 7 ) with:

3 = Wednesday

4 = Thursday

5 = Friday

6 = Saturday

7 = Sunday

8 = Monday

9 = Tuesday

so your calc can be even writed so:

Get ( CurrentDate ) + DayOfWeek ( 6 - DayofWeek ( Get ( CurrentDate ) ) + 4 )

where the last number is the day of week number.

I would never have guessed that days of the week could be 8+

The RESULT of DayOfWeek() is ALWAYS a number between 1 and 7, inclusive. However the PARAMETER of the function must be a date. If it's not, then Filemaker tries to convert it to a date.

Any number between 1 and 1460970, inclusive, can and will be converted to a valid date in the Filemaker calendar (i.e. to one of the 1460970 days between Jan 1, 0001 and Dec 31, 4000).

the number can go from 3 to 9

Since DayofWeek() can return any number between 1 and 7, the expression

7 - DayofWeek ( Get(CurrentDate) )

will return 0 on a Saturday. And 0 or a negative number CANNOT be converted to a valid date. Therefore, the next step:

DayOfWeek ( 7 - DayofWeek ( Get(CurrentDate) ) )

will fail to evaluate.

And 0 or a negative number CANNOT be converted to a valid date.

Unexceptionable reasoning.

So also THIS time you are right ! :

To give evidence to the day of week number the calc must start from 13:

Get ( CurrentDate ) + DayOfWeek ( 13 - DayofWeek ( Get ( CurrentDate ) ) + 4 )

where the last number is the day of week number.

I haven't tested this, but it doesn't look right: if today is Friday, and I'm looking for the next Saturday - that's 13 already.

So also THIS time you are right !

You sound surprised… :

DayofWeek ( 7 ) = 1

DayofWeek ( 14 ) = 1

So:

Get ( CurrentDate ) + DayOfWeek ( 13 - 6 + 7 )

=

Get ( CurrentDate ) + DayOfWeek ( 14 )

=

Get ( CurrentDate ) + 1

So, if today is Friday, Saturday will be tomorrow. the next Saturday will be tomorrow.

Edited October 13, 200817 yr by Guest
too much LOL

So, if today is Friday, Saturday will be tomorrow.

Unexceptionable reasoning. :

Oops, you got me this time. I got confused by the last term being positive. I believe a more conventional notation would be:

4 + 13 - DayofWeek ( Get ( CurrentDate ) )

(all positive terms before the negative ones)

4 + 13 - DayofWeek ( Get ( CurrentDate ) )

(all positive terms before the negative ones)

Good point.

However I'm feeling that your calc can be simplified somewere. ( not for sure and no other time to give it )

Yes, it can - if you want to generalize it. But I didn't go for that - I hardcoded for Wednesday, and it cannot get much simpler than the original:

d + DayOfWeek ( 10 - DayofWeek ( d ) )