Comparing Week of Date between two years

[James Gill]

Hopefully I'm not the only one dealing with some date issues with the turn of the new year.

 

I have a fairly simple formula that I want to make sure will work correctly. Say I wanted to get the WeekOfYear for the date "12/30/2012", filemaker would return the numerical value of 53.  WeekOfYear ( "12/30/2012" )  ≥ WeekOfYear ( "10/1/2012" ) would return True, as 53 is greater than 40.

 

However, when the new year turns over and now we are evaluating the formula as WeekOfYear ( "1/1/2013" )  ≥ WeekOfYear ( "10/1/2012" ) the formula now returns false, as 1 is not greater than 40.

 

My question is, if I change this formula to read Get ( CurrentDate ) ≥ WeekOfYear ( "10/1/2012" ) I get a result of True.  Knowing how Filemaker calculates dates, is the calculation engine powerful enough to know that I want to evaluate the week AND year of the Current date without actually specifically WeekOfYear or is it simply comparing the numerical values of the number of days since 1/1/0001 (i.e. GetAsNumber ( Get ( CurrentDate ) ) ≥ GetAsNumber ( WeekOfYear ( "10/1/2012" ) = True ) 

[comment]

The latter.

[James Gill]

Well that stinks.  What's the best way to account for this?  I'm dealing with a client that's extremely finicky with regard to how their AP system calculates payables.  They have a payment date that is calculated based upon payment terms and a payment schedule that is weekly and starts on Monday.  They do not want to see payables appear in their system until the beginning of the business week on which the payment is due.  This was the idea behind comparing the week of the current date against the week of the payment date; as long as the date of the week of the payment was less than that of the current date, the payment was not due.  (Obviously I didn't think about years when coming up with that solution, d'oh...)  When 2012 wrapped up and 2013 rolled around, all of a sudden the payables weren't due since now the week number of the payment date was greater than the week number of the current date.

[comment]

I don't fully understand the problem here. it seems you want to calculate something that is common to all dates of a week, regardless of the year - sort of an absolute week number. I usually use the date of the starting day of the week for this purpose, but you could also use a number =

Div ( YourDate - 1 ; 7 )

for this purpose.

[James Gill]

Absolute week number of a particular date is the perfect term to use in this case and it appears as if your solution works well enough. Thank you!  

 

The problem here is that if a payment was due during the 50th week of a year and the calculation I was using to determine if a payment was due is "WeekOfYear ( Get ( CurrentDate ) ) ≥ WeekOfYear ( PaymentDueDate )" then once the new year rolls around, the calculation is evaluated as "WeekOfYear ( 1 ) ≥ WeekOfYear ( 50 )" which obviously fails.

 

I'm curious as to how using the Date of the starting day of the week gets around that issue?

[comment]

I'm curious as to how using the Date of the starting day of the week gets around that issue?

 

It's the same thing, only in a different format. Calculate =

YourDate - DayOfWeek ( YourDate - 1 ) + 1

to return the starting Monday of the week of YourDate. Mathematically, it's the same as the absolute week number, only multiplied by 7.