1. ## WEEK NUMBERS

Anybody out there ever had the need to convert a date into a WEEK NUMBER. Not sure whether this is a UK format. For those not familiar with this term, week numbers run from 1 to 52 starting 1st January each year. Pretty obvious.
I need to check if two order dates occur in the same week number and in the same year. Technically the change over from week 52 in one year to week 1 in the following year would not matter.
Regards,
Ian Stewart

2. ## RE: WEEK NUMBERS

You'll need to setup/build a table to contain date ranges for weeks and corresponding week numbers for a number of years into the future (decide for yourself how far into the future your application will be used).

"week numbers run from 1 to 52 starting 1st January each year" - fine in concept but in real usage there have to be 53 weeks periodically/eventually (usually five or six or seven years) to keep the sequence in some sort of correlation to the calendar.

Once you have the table built indicating where the fifty-third week falls, you do a lookup to that table based on the calendar date to return a week number.

3. ## RE: WEEK NUMBERS

Upon reconsideration of your post, my answer is incorrect, but still pertinent.

What do you do with the 365th day of the year? Is that week 53 in and of itself?

The procedure would seem to be to obtain the "day of the year", a number, then divide by 7 to obtain the "raw" week number (with decimal), then get the floor() of that number.

I'd be glad to pursue the computation of the parts involved if this is not sufficient to your needs.

4. ## RE: WEEK NUMBERS

FLOOR((JDATE(DATE())-JDATE(CTOD("01/01/"+RIGHT(DTOC(DATE()),4))))/7)+1

= 5.000000

I've always worked with Alpha Five in some version or another. I'm assuming that floor(), jdate(), and ctod() are valid in Alpha Four.

If you are passing a variable or field value to the expression...
g
FLOOR((JDATE(datevalue)-JDATE(CTOD("01/01/"+RIGHT(DTOC(datevalue),4))))/7)+1

5. ## RE: WEEK NUMBERS

Stan,
Many thanks for your efforts. Unfortunately jdate is not available in Alpha4. I am however in the process of converting my applications to Alpha5 and with the jdate function the problem is easily solved. Again many thanks
IAN

6. ## RE: WEEK NUMBERS

If this requirement is for the current year only, then a solution would be to use a CASE() funtion or a script with the CASE{} command.

You could compare the record's date against a hard-coded list of week ending dates to give you the week number.

This is by no means an elegent solution, but it would work.

gm

7. ## RE: WEEK NUMBERS

Thanks Gedi,
Unfortunately there is not a case() function in Alpha4v6 although there is a case() statement in the script language.
This whole query started off hoping their was a simple solution. It's not surprising as I did a internet search on "week numbers" and nobody seems to agree on what they are. They can't even agree what day a week starts with.
I think Stan's solution using the jdate function in Alpha 5 will probably be the best.
Many thanks,
IAN

8. ## RE: WEEK NUMBERS

Ian;
Actually I believe A4V6 is the first version of A4 with the "Case" function. By the way, is "jdate" "Julian Date"? If so, I believe it would not be difficult to construct a look-up table that would take care of your needs.

Duncan

9. ## RE: WEEK NUMBERS

Thanks Duncan,
Your right, I do have the case statement available (problem with using an old manual). Jdate apparently is a Julian date function in Alpha5 which would solve the problem (see Stan Mathews response above).
Thanks
Ian

10. ## RE: WEEK NUMBERS

Stan,
Just to let you know I have solved my problem. I was beginning to lose sight of what I wanted to acheive, ie. checking if two dates occurred in the same week number. Since it doesn't matter what the actual week number is, 5,50,100 or whatever, I generated my own week numbers from an arbitary date (Sun 6 Jan 2002) and calculated a week number for each of the two dates to be compared.
I had forgotten that you can actually subtract two dates in Alpha4 which generates a number. You can't do any arithmetic calculations on this number until it is enclosed in ().
The code I used to generate a week number for each of the two dates is:
FLOOR((DATE-CTOD("6-1-2002"))/7)+1

Again, thanks for your help, it was the jdate idea that set me on the right track.

IAN

11. ## RE: WEEK NUMBERS

Glad to hear it. I was just experimenting and, of course, you can subtract dates in Alpha Five as well. I had tried that early in my attempt to suggest a solution and must have had a typo in my efforts because it didn't work correctly. That's when I went to jdate().

12. ## RE: WEEK NUMBERS

This is a script that will calculate the week number. There are two versions. Script 1 needs all three lines. Script 2 is all one line. These scripts use A4 V3 functions. They assume that a week is Sunday through Saturday. There could be 53 weeks in a year.

Enter_Date is a date field in my table.
I hope this helps.

Stans script fails to take into account that the first day of the year is not always Sunday.

'Script 1****************************************

vndow = (dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(Enter_Date)
vnumber = if(vndow " 1.00,1.00,vndow)
ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + vnumber ) / 7)

'Script 2****************************************

ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + (if((dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(enter_date)"1.00,1.00,(dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(enter_date))) ) / 7)

13. ## RE: WEEK NUMBERS

"week numbers run from 1 to 52 starting 1st January each year"

So "the first day of the year is not always Sunday" but it is always January 1, as the post stated.

14. ## RE: WEEK NUMBERS

Stan my apologies I misunderstood if a week starts Jan 01 and ends Jan 07 than this will work for V4 with enter_date a field in the table.

ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + 1 ) / 7)

15. ## RE: WEEK NUMBERS

This might be better

floor((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) ) / 7) + 1

16. ## RE: WEEK NUMBERS

One more time

floor((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + 1 ) / 7)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•