weeknum

[reno]

a customer supplies sales data on their fiscal week and their weeks run
saturday thru friday midnight and are of the format YYYYWK (200801 thru
200853). the fiscal period starts on first saturday in feb and runs thru
last friday in january. is the only way to get the calendar dates for their
weeks to set up a lookup table--weeknum() won't work thx reno

 

[edvwvw via OfficeKB.com]

a customer supplies sales data on their fiscal week and their weeks run
saturday thru friday midnight and are of the format YYYYWK (200801 thru
200853). the fiscal period starts on first saturday in feb and runs thru
last friday in january. is the only way to get the calendar dates for their
weeks to set up a lookup table--weeknum() won't work thx reno

There are a variety of ways that this could be done - a LOOKUP table for each
week on a seperate sheet or a formula method. I cannot find a way to generate
the first Saturday in February so I have checked the dates up to 2011 and
included it in this solution.

Cell A2 YEAR Cell B2 Week number then in C2 the formula below

=LOOKUP(A2,{2008,2009,2010,2011;"02/02/2008","07/02/2009","06/02/2010",
"05/02/2011"})+(B2*7)-7

This will return the date of the Saturday in the week that is stipulated.


edvwvw