Week of the month

[nick marodis]

Does anyone have a function that will take a date and return a number for the week in the month that date falls in? Like 1st week of the month 2nd week of the month etc.

Thanks for any help
Nick

[whanigsberg]

RE: Week of the month

Nick,

Say the week starts on Sunday. Assume the first of the month is Saturday. From your point of view is Monday in the second week of the month?

A function should not be difficult to write so long as we agree on what it should do.

Bill

[nick marodis]

RE: Week of the month

Bill, Thanks for the reply.
Are you saying that i should get the first day of the month and then work from there?

Looking for ideas

Nick

[nick marodis]

RE: Week of the month

Bill,
After reading you reply again I think i missed the mark. In response to question I think monday would fall in the first week of the month in your scenario.

Thanks
Nick

[whanigsberg]

RE: Week of the month

Hi again Nick,

I've been musing about this and it is a bit of a puzzle.

About getting the terms of reference defined, I was thinking about how you want to handle fractional weeks.

The alpha5 dow function takes a date argument and returns an interger corresponding to the day of the week. It employs the convention that the week begins on Sunday. That is Sunday is equal to 1. That's why I asked whether the week begins on Sunday.

Suppose the first of the month is Wednesday. Does the partial week from there to Saturday need to count as a week for your purposes?

On the other end, suppose the date you're interested in is a Tuesday. Does the interval between Sunday and Tuesday count as another week?

It all depends on what you need to do with the result.

I would first calculate the full weeks and then add (or not add)the fractions at either end.

Find the date of the first Sunday in the month. If the date is not the first of the month there is a fraction to add.

Then do something similar at the other end. If the difference between the date of the first Sunday and your date divided by 7 leaves a remainder then there is a partial week to add at the end (I think).

If I had this calculation to make, I would put it in a custom function.

You could do all this or have a cool drink. From the point of view of your mental health, you probably should consider the latter.

Bill

[JerryBrightbill]

RE: Week of the month

Bill and Nick,

Excessive coffee and work avoidance is a wonderful combination. Interesting challenge. Below are two functions to give week numbers and a test script to show how they work. The first function gives the week of the month starting with partial weeks. The second function starts counting on the first full week. Hope this is what you are looking for.

Function to find week starting with partial weeks

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

[nick marodis]

RE: Week of the month

WOW! Jerry I'm overwhelmed by your response. With your suggestion it looks like I will get to do more than drink coffee, thought it does help.
I was thinking along the lines of getting the first day of the month and using dow() to give me which day of the week it was then start from that point.

With help from people such as you I will be able to save a few brain cells.

Thanks Jerry & Bill
Nick

[whanigsberg]

RE: Week of the month

Jerry,

That's very sweet, no question about it.

It made me think that I don't drink enough coffee, which would astonish those who know me. (The work avoidance part is no problem.)

Actually, I had just about finished a similar function last night when I got too sleepy to think straight. The interesting thing is that my approach though similar was not identical.

If I understand completely, Jerry's approach is based on what could be termed relative weeks. If the month starts on Wednesday and the test date is two Wednesdays later then the function yields two weeks.

My approach used what could be thought of as absolute weeks. They always start on Sunday. In this approach that same Wednesday would be in the third week. (Unless I've confused myself here.)

It's interesting that such a seemingly simple idea can be operationalized in at least 3 ways. I wonder if there is a convention on how this is figured.

Regards to you both with thanks for the colleagueship.

Bill

[JerryBrightbill]

RE: Week of the month

Bill,
After too many cups of coffee, I tried to simplify the concept. If I read your answer correctly, (the coffee has worn off) I am using absolute weeks. A week is defined as Sun to Sat. The first function assumed that week one was the week where the first day of the month fell, even if it was a Saturday. I calculated how many days the first Sunday fell after the first day and used that to determine the weeks. Examples:

July 1, 2000 is a Saturday and is week 1. The dow for the first is 7
July 2 is the first Sunday and day_wk_two calculates as 2. Any date less than the 2nd is week 1
July 2 to 8 Is week 2

October 1, 2000 is a Sunday and Oct 1 to 7 is week 1. The dow for the first is 1
October 1 is the first Sunday and day_wk_two calculates as 8. Any date less than the 8th is week 1
Oct 8-14 is week 2

The second function assumed only full weeks starting on the first Sunday. The second function found the first Sunday of the month and called that the start of week one. Examples:

July 1, 2000 is a Saturday and is week 0 - Not a full week
July 2 is the first Sunday and day_wk_two calculates as 2. Any date less than the 2nd is week 0
July 2 to 8 Is week 1 - first full week

October 1, 2000 is a Sunday and Oct 1 to 7 is week 1
Oct 8-14 is week 2

Does this make sense? Actually relative weeks would be very easy. The first 7 days would be week 1, the next 7 week 2 and so on. I think I need more coffee.

Jerry

[JerryBrightbill]

RE: Week of the month

Just thought of another complication. If the last week is less than 7 days, does it get counted as a week? Not sure I want to go there right now!

Jerry

[JerryBrightbill]

RE: Week of the month

Bill,
Just tested my functions and found function 2 didn't work as advertised if the first was a Sunday. Here is revised script for the function.

Function to find week full weeks only

function WeekFull as N(select_date as D)
if alltrim(cdate(select_date))=""
weekfull = 0
end
end if
'//// Next 5 lines new
if dow(ctod(stuff(dtoc(select_date),4,2,"01"))) = 1
day_wk_two = 1
else
day_wk_two = 9 - dow(ctod(stuff(dtoc(select_date),4,2,"01")))
end if
day_mth = val(right(cdate(select_date),2))
select
case day_mth - day_wk_two

[whanigsberg]

RE: Week of the month

That's funny.

Mine broke in the very same place and I had to work with it.

Thanks,
Bill

[csda1]

RE: Week of the month

Nick & all,

Below is a function to return the week of the month for the generic case. Depending upon the situation and your definition of the week of the month, you may have to modify it a bit. It essentially returns the Week # of the month for Dateval starting on the DayofWeek # (1 to 7 is Sun to Sat respectively). Thus the 1st 7 days are week 1.x, days 8-15 are week 2.x, etc. If you don't want fractional values, just take the INT() of the result.


function MonthWeek as N(DateVal as D,DayOfWeek as N)

' Returns week number of DateVal's Month.
' If DayofWeek=0, then week 1 starts on 1st of month
' If DayOfWeek=1 to 7, then the week begins on that day (Sun-Sat)
' If DateVal={}, then it returns 0
' Fractional weeks can be truncated with an INT() if desired
' Days before the 1st week are like the last days of week 0 (e.g. 0.857143)

dayn=day(DateVal)
day1=DOW(DateVal-Day(DateVal)+1)

IF DayOfWeek>0
dayn=dayn-DayofWeek+day1+if(DayofWeek>day1,-7,0)
END IF

MonthWeek=iif(DateVal={},0,((dayn-1)/7)+1)

'Uncomment the next line if you only want an integer
' MonthWeek=Int(MonthWeek)

end function


Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]