Find the Month from Week Number and Day of week

[Ziggy1]

I know the Week number, Day of Week and Year ( it is a production batch Expiry)

eg

Week= 24
Year = 09
Day of Week = 5

I want to convert to a date, but I need to work backwards. So The day of the week relates to the week number which is important when a week overlaps 2 months.

Any ideas to point me in the right direction...I'm searching also but the results bring up a variety of unrelated.

thanks

 

[raskew]

Hi -

Try playing with this:

Wk = 24
WD = 4
Yr = 09

x = dateadd("ww", wk, dateserial(yr, 1, 1)) 
y = x - iif(weekday(x)<> wd, weekday(x), 0) + iif(weekday(x)<> wd, wd, 0)
? y
6/17/2009

I didn't test for all possibilities but it should give you a starting point.

Best Wishes - Bob

 

[khawar]

Paste the function below in a module

Function GetMonthfromDayofWeek(WkNum As Integer, DyOfWk As Integer, Yr As Integer) As Integer
GetMonthfromDayofWeek = Month(CDate((DateSerial(Yr, 1, 1) + ((WkNum - 1) * 7)) + DyOfWk - Weekday(DateSerial(Yr, 1, 1))))
End Function

and use like this in your case

GetMonthfromDayofWeek(24,5,2009)

it will calculate date as June 11, 2009 and will show you month 6

It will consider sunday as the first day of week you can ammend it as per your requirement

 

[Ziggy1]

Thank you both, this is great....both seem to work, I'll let you know if any issues.

Ziggy

 

[Ziggy1]

This is an example a coworker came up with....

Calc1: DateSerial([yearNumber],1,([WeekNumber]-1)*7+1)


The Date serial function adds the days greater than the given month ( I didn't know that), so starting at Jan 1 once the DAY excedes 31 it is added, thus multiplying the Week number x 7 provides the number of days the week number represents.

khawar you are on the same page, as you used similar logic ( I know it was for the Month).