How to calculate date of the next Wednesday?

[Andrew5]

How to calculate the date of the next Wednesday?

Any suggestions out there?

[Raybaudi]

this is a way

next_Wednesday.zip

[Andrew5]

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.

[comment]

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, 2008 by Guest
Fixed a typo

[Andrew5]

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.

[comment]

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

[Raybaudi]

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

[comment]

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

[Andrew5]

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

[Raybaudi]

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.

[comment]

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.

[Raybaudi]

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.

[comment]

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… :

[Raybaudi]

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, 2008 by Guest
too much LOL

[Raybaudi]

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

Unexceptionable reasoning. :

[comment]

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)

[Raybaudi]

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 )

[comment]

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 ) )