Date Calculation

[Al Cole]

Does anyone know how to write a formula for a date field to give the week it occured in. For example if the date field contained 01/01/01 - 01/06/01 would return Week 1
01/14/01 - 01/20/01 would return Week 3 etc.

Thanks
Al

[Jeff Moses]

RE: Date Calculation

Al, I think you could do this without too much trouble if you use a WHILE loop. If your script started out with 01/01/01 and added 7 each time it went through the WHILE you could be counting the times around. When the value of the next date plus 7 reached less than or equal to your target date you could break the loop and your counter would have the number of times it went around to get there and that would also be the week. Let me know if you want an example, I think I have one in one of our apps. I'll look it up and send it to you.
Jeff

[Tom Cone Jr]

RE: Date Calculation

Al, how will you define a 'week'? The first seven days of the year might actually fall into separate weeks, if you see what I mean.

-- tom

[JerryBrightbill]

RE: Date Calculation

Al,

Here is a function that will give the week within a month.

function Week as N(DateVal as D)
if alltrim(cdate(DateVal))=""
week = 0
exit function
end if
day_wk_two = 9 - dow(ctod(stuff(dtoc(DateVal),4,2,"01")))
day_mth = val(right(cdate(DateVal),2))

select
case day_mth - day_wk_two

[Al Cole]

RE: Date Calculation

Thanks Jeff for your response, If you could locate an example and send it to me that would be greatly appreaciated.

You can email me at [email protected]

Thanks Al

[csda1]

RE: Date Calculation

Jerry,

Here are some improvements for your code.

[b]
function Week as N(DateVal as D)
if alltrim(cdate(DateVal))=""
week = 0
exit function
end if

' The following line has the advantage of returning the Day-of-the-Week for the '1st of the month (same as for 2nd week) and works no matter whether American or 'European dat formats
day_wk_two = 9- dow(Dateval-DAY(Dateval)+1)

' Simpler way to get day of the month
day_mth = day(DateVal)

'Do the calculation once, not for each case
diff=day_mth-day_wk_two

select
case diff

[csda1]

RE: Date Calculation

Tom and Al,

Attached is my YEARWEEK() function that takes this very issue into account. I have added some comments for others to try to follow. I'm not going to even try to explain the starting day-of-week adjusment math. That's for those mathematically inclined. The header is shown below, but the entire code is in the attachment.

[b]
function YearWeek as N(DateVal as D,DayOfWeek as N)
' Purpose: This function returns the week number of the year

' Input Parameters:
' DateVal - Any Valid Date Value/expression
' DayOfWeek - A number from 0 to 7
' 0 means Jan 1st is 1st day of 1st week of year
' 1 (Sunday) through 7 (Saturday) means that the 1st week of the
' year to count starts on that day

' Output (Return Value):
' Returns week number of DateVal's Year (0 through 53) or
' -1 for error conditions

' If DayofWeek=0, then starts week count from 1st of the year
' If DayOfWeek=1 to 7, then week count begins on 1st day of year
' with that Day of Week. Any day of year before that returns a 0
' If DateVal={} then returns 0
' If DayOfWeek>7 or DayOfWeek

[Al Cole]

RE: Date Calculation

Thank you all for your response to my inquire, I think I have enough info to try to tackle my task.

Al

[JerryBrightbill]

RE: Date Calculation

Thanks Ira,

There always seems to be more than 1 way to get the same result. Since I don't work with european date formats, I never thought much about how that would change the code.

Jerry

[Ron McCormack]

RE: Date Calculation

This could work
set a date to be prime 
your date could be set however you wish
e.g.
   prime=ctod("01/01/01")
   your_date=ctod("01/14/01")
   week_no=mod(your_date-prime,7)
hope it is Ok

Ron

[whanigsberg]

RE: Date Calculation

In Montreal half the machines seem to be N.American and half European. Lots of stuff breaks up here. Last week I got an insurance claim form from my dentist showing that I was born in the 47th month of 1904 or something similar. (I exagerate only slightly.) It actually made me feel good to see professional code with the same errors I *used to* make. Of course I have now moved on to *new* errors!

Bill