DATEDIFF Function Weeks

[jmarcrum]

Hey eveyone,

Here's what i got...I got 2 fields in a Table, WeekNum and Year, let's say WeekNum=42 and Year=2008. On a form I created, a user can click a button and a textbox will pop up allowing the user to enter a WeekNum and Year. I want to compare that new WeekNum and Year with the one already in the Table (WeekNum=42 and Year=2008) and if it's DIFFERENCE is <= 18 months, Exit the sub procedure. Is there a function for this type of calculation?

Thanks SOOOO much!!!

[Stewart Ross]

Hi. There is no built-in function I know of which takes a week number and a year and calculates the difference in months. I reckon you will need to devise a custom function to do so. However, so that you are calculating something that actually matches your needs you need to check your assumptions first:

* Is Week 1 taken to start 1 Jan each year? If it isn't, does week 1 start on the first working day of each year, and if so how is that defined?

* Since 52 weeks corresponds to 364 days, to what week is the 365th day of a normal year, and the 366th in a leap year, assigned?

* What is your definition of 18 months? Is it 52 + 26 weeks, or six calendar months beyond the year anniversary? Months are of different lengths, after all, as are years themselves...

If you can answer these questions directly you will have a head start in devising a custom function to calculate the 18 months concerned.

-Stewart