Determine date based upon week number

[CDeCinko]

Anyone have a method that will return the first or last day (date) of the week when only the week number is known?

[MikeC]

Re: Determine date based upon week number

Connie,
Not saying that I can do this off the top of my head but think it could be done with some manipulation of the current functions Alpha has....but regardless there HAS to be some reference point or it is impossible--given a week number who is to say it is for this year, last year, 20 years ago???? And then to have a function span more than one year could be even trickier.

[CDeCinko]

Re: Determine date based upon week number

Very true, yes. In my instance I am working with dates in the future which helps to narrow it down. None would go into the future more than a year.

[MoGrace]

Re: Determine date based upon week number

You could try a function to get a default date that you could use with a calendar control.

FUNCTION find_date AS D (week_no AS N)
	dim nw as n
	dim nd as d
	dim vcStart as c
	dim start as d
	vcstart = "01/01/"+cyear(date())
	start = ctod(vcStart)
	nw = week_no * 7
	nd = start + nw
	find_date = nd
END FUNCTION

?find_date(25)
={06/24/2008}

Which is the 3rd day of the week - Tuesday.

Your date field could have a field rule for the default value that uses the field that has the week_no value. Which I did not test yet.

find_date(week_no)

If you want to go past the current year, you could add another parameter to the function to set the start date to the first day of the year you want, which the user would have to input.

FUNCTION find_year AS D (week_no AS N,start as d)
	dim nw as n
	dim nd as d
	nw = week_no * 7
	nd = start + nw
	find_year = nd
END FUNCTION

?find_year(25,{01/01/2009})
={06/25/2009}

[G Gabriel]

Re: Determine date based upon week number

dim a as n=16
dim b as n=2008
[B]?date_value(b,1,1)-dow(date_value(b,1,1))+1+(a-1)*7[/B]
= {04/13/2008}
[COLOR="RoyalBlue"]'The Sunday (first day) of week 16 of the year 2008[/COLOR]
[B]?date_value(b,1,1)-dow(date_value(b,1,1))+7+(a-1)*7[/B]
= {04/19/2008}
[COLOR="RoyalBlue"]'The Saturday (last day) of week 16 of the year 2008[/COLOR]

a=9
b=2009
[B]?date_value(b,1,1)-dow(date_value(b,1,1))+1+(a-1)*7[/B]
= {02/22/2009}
[COLOR="RoyalBlue"]'The Sunday of week 9 of the year 2009[/COLOR]

[B]?date_value(b,1,1)-dow(date_value(b,1,1))+7+(a-1)*7[/B]
= {02/28/2009}
[COLOR="RoyalBlue"]'The Saturday of week 9 of the year 2009[/COLOR]

You could make a function out of this.

[Stan Mathews]

Re: Determine date based upon week number

A different take on the function.

FUNCTION wkfirst AS D (wk_num AS N, yr_num AS N )
	if (.not. between(wk_num,1,53))
		ui_msg_box("Sorry","Invalid week number.")
	end if
	select
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/07/"+alltrim(str(yr_num))))
			'first day of year is a Sunday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)
			
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/06/"+alltrim(str(yr_num))))
			'first day of year is a Monday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-1
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +1
			end if
		
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/05/"+alltrim(str(yr_num))))
			'first day of year is a Tuesday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-2
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +2
			end if
			
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/04/"+alltrim(str(yr_num))))
			'first day of year is a Wednesday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-3
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +3
			end if
			
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/03/"+alltrim(str(yr_num))))
			'first day of year is a Thursday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-4
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +4
			end if
			
		case week(ctod("01/01/"+alltrim(str(yr_num)))) = week(ctod("01/02/"+alltrim(str(yr_num))))
			'first day of year is a Friday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-5
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +5
			end if
			
		case else
			'first day of year is a Saturday
			wkfirst = ctod("01/01/"+alltrim(str(yr_num)))+ ((wk_num-1)*7)-6
			if year(wkfirst)< yr_num
				wkfirst = wkfirst +6
			end if
			
	end select
			
END FUNCTION

[G Gabriel]

Re: Determine date based upon week number

Here it is in a function.

This function determines what date is the Sunday of a certain week #:

FUNCTION week_Sun AS D (Week_No AS N, Year AS N )	
week_Sun=date_value(year,1,1)-dow(date_value(year,1,1))+1+(week_no-1)*7
END FUNCTION

?week_sun(9,2009)
= {02/22/2009}

You could make one for Monday, Tuesday..etc.

FUNCTION week_Mon AS D (Week_No AS N, Year AS N )
week_Mon=date_value(year,1,1)-dow(date_value(year,1,1))+2+(week_no-1)*7
END FUNCTION

?week_mon(9,2009)
= {02/23/2009}

[G Gabriel]

Re: Determine date based upon week number

And if you want just one function that does it all:

FUNCTION Date_From_Week_NO AS D (Week_day as C,Week_No AS N, Year AS N )
x=case(Week_day="sun",1,Week_day="mon",2,Week_day="tue",3,Week_day="wed",
4,Week_day="thu",5,Week_day="fri",6,.t.,7)
Date_From_Week_NO=date_value(year,1,1)-dow(date_value(year,1,1))+x+(week_no-1)*7
END FUNCTION

?date_from_week_no("sun",9,2009)
= {02/22/2009}

?date_from_week_no("mon",9,2009)
= {02/23/2009}

?date_from_week_no("tue",9,2009)
= {02/24/2009}

...and so on..

[G Gabriel]

Re: Determine date based upon week number

..and..
further simplified:

FUNCTION Date_From_Week_NO AS D (Week_day as C,Week_No AS N, Year AS N )
Date_From_Week_NO=date_value(year,1,1)-dow(date_value(year,1,1))+
day_number(Week_day)+(week_no-1)*7
END FUNCTION

[MoGrace]

Re: Determine date based upon week number

..and..
further simplified:

FUNCTION Date_From_Week_NO AS D (Week_day as C,Week_No AS N, Year AS N )
Date_From_Week_NO=date_value(year,1,1)-dow(date_value(year,1,1))+
day_number(Week_day)+(week_no-1)*7
END FUNCTION

That's great Gabe, I hope Connie sees it.

[CDeCinko]

Re: Determine date based upon week number

Wow! Amazing how quickly you can come up with the solution. Thanks!

[Mayapur]

Re: Determine date based upon week number

The answers so far may have problems with the first and last weeks of a year, and don't forget some years have 54 weeks.

To stay within a given week number you may want to try the following, which work for future week numbers including the year 2028 (the next 54 week year). You never know, you may be using your program then even though it will be outdated. I am still using a DOS based FoxBase program I wrote in the 1980's, but desperately trying to learn Alpha5 to get out of it.

FUNCTION weekstart AS D (wk AS N, yr AS N )
	'Finds the first day of a given week and year
	if wk > 53 then
		wk = 53
		if yr = 2028 then
		    wk = 54
		end if
	end if		
	if wk < 2 then
	    weekstart = date_value(yr,1,1)
	else
	    weekstart = date_value(yr,1,1)+8-dow(date_value(yr,1,1))+7*(wk-2)
	end if
END FUNCTION

FUNCTION weekend AS D (wk AS N, yr AS N )
	'Finds the last day of a given week and year
	if wk < 2 then
	    weekend = date_value(yr,1,1)+7-dow(date_value(yr,1,1))
	else
	    weekend = date_value(yr,1,1)+8-dow(date_value(yr,1,1))+7*(wk-2)+6
	    if year(weekend) > yr then
	        weekend = date_value(yr,12,31)
	    end if
	end if
END FUNCTION

[G Gabriel]

Re: Determine date based upon week number

What problem are you refering to?

?Date_From_Week_NO("sun",1,2008)
= {12/30/2007}

?Date_From_Week_NO("sun",1,2028)
= {12/26/2027}

?Date_From_Week_NO("sun",53,2028)
= {12/24/2028}

?Date_From_Week_NO("sun",54,2028)
= {12/31/2028}

The original question states that the number of the week is" known".

[G Gabriel]

Re: Determine date based upon week number

If you want yet a more comerhensive function to accomodate for the possibility that the week number may or may not be correct, that is not hard, but also not needed as alpha arleady has a built-in function to tell you the week number:
week().

So, if you use that function along with this UDF, you are all set. You could, if you want, incorporate the week() function in the UDF, as in:

FUNCTION Date_From_Week_NO AS D (Week_day as C,Date AS D, Year AS N )
Date_From_Week_NO=date_value(year,1,1)-dow(date_value(year,1,1))+
day_number(Week_day)+(week(date)-1)*7
END FUNCTION

and that should do it for you.

[G Gabriel]

Re: Determine date based upon week number

I even simplified it a little more and now all you need is two parameters only, the weekday and the date in question:

FUNCTION Date_From_Week_NO1 AS D (Week_day as C,Date AS D)
Date_From_Week_NO1=date_value(year(date),1,1)-dow(date_value(year(date),
1,1))+day_number(Week(date))+(week(date)-1)*7
END FUNCTION

?Date_From_Week_NO1("sun",{12/31/2028})
= {12/31/2028}

?Date_From_Week_NO1("sun",{12/29/2028})
= {12/24/2028}

So, now you got 2 functions, take your pick.