Andrew5 Posted October 12, 2008 Posted October 12, 2008 How to calculate the date of the next Wednesday? Any suggestions out there?
Andrew5 Posted October 12, 2008 Author Posted October 12, 2008 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 Posted October 12, 2008 Posted October 12, 2008 (edited) 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 Posted October 12, 2008 Author Posted October 12, 2008 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 Posted October 12, 2008 Posted October 12, 2008 Sorry, an extra parenthesis slipped in during copy/pasting. Fixed now.
Raybaudi Posted October 12, 2008 Posted October 12, 2008 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 Posted October 13, 2008 Posted October 13, 2008 Actually, it needs to start at 8 as the minimum, or it will fail on a Saturday.
Andrew5 Posted October 13, 2008 Author Posted October 13, 2008 I see. I would never have guessed that days of the week could be 8+..... Filemaker just really surprises me sometimes.. Thanks.
Raybaudi Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 (edited) 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 Posted October 13, 2008 Posted October 13, 2008 So, if today is Friday, Saturday will be tomorrow. Unexceptionable reasoning. :
comment Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 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 Posted October 13, 2008 Posted October 13, 2008 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 ) )
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now