You didn't give the full relationship between FW numbers and dates.
From what you have said, I am assuming that FW numbers are based on:
weeks with Saturday as the first day of the week
and FW 1 starts on the first Saturday in July each year
The first Saturday in July is a variable date - this year (2010) it is 03 July, next year it is 02 July and in 2012 it is 07 July
I have created a User Defined function that you can use as a normal formula. You enter a reference to a cell containing a date and the function returns FW n, where n is the week number.
The function should be correct for any year - it calculates the date for the first Saturday of the year.
To use this, open your Workbook.
Click Alt+f11 (The Alt key and function key number 11 at the same time)
This opens the Visual Basic Window.
In the Project Explorer window (usually on left), find 'VBAProject(YourWorkbook.xls)'
Right click on it and select 'Insert' then 'Module' (not Class Module)
Double click Module1 which is under the Modules folder
Enter the following code in the main window.
Public Function WeekRef(dtWeek As Date) As Variant
Dim dtFirstSat As Date
Dim intBaseYear As Integer
Dim dtFirstJuly As Date
Dim dtBase As Date
Dim intWkNum As Integer
Dim n As Integer
On Error GoTo ErrHnd
'get start of this week
If Weekday(dtWeek) = 7 Then
dtStartOfWk = dtWeek
dtStartOfWk = dtWeek - Weekday(dtWeek)
'get first Saturday in this July
For n = 0 To 6
If Weekday(DateSerial(Year(dtWeek), 7, 1) + n) = 7 Then
dtFirstSat = DateSerial(Year(dtWeek), 7, 1) + n
'get year - this year if after 1st Saturday in July - else last
'test if this date is On or after first Saturday in July
If dtWeek >= dtFirstSat Then
'starting point is this year
intBaseYear = Year(dtWeek)
'starting point is last year
intBaseYear = Year(dtWeek) - 1
'get 1st Saturday of the base year
dtFirstJuly = DateSerial(intBaseYear, 7, 1)
'test first seven days for Saturday
For n = 0 To 6
If Weekday(dtFirstJuly + n) = 7 Then
dtBase = dtFirstJuly + n
'calculate difference between start of this week and base date
'divide by 7 to get week number -1, so add 1
intWkNum = ((dtStartOfWk - dtBase) / 7) + 1
WeekRef = "FW " & CStr(intWkNum)
WeekRef = CVErr(xlErrNA)
Click File-Save from the VB Menu
Use Alt+f11 to return to the main Excel window
Enter a date in say cell A1. If cell A1 contains "2-Mar-10"
Enter this in any other cell
=weekref(A1)The function will return "FW 35"
You can include a date directly in the function
=weekref("14/Oct/10")The function returns "FW 15"
You can place any calculation which returns a valid Excel-recognized date inside the weekref function.
You asked would this be possible for Months as well?
I would need more information to be able to do something along the same lines.
What code is returned - is it FW 1 to FW 12 or FM 1 to FM12 and is it just Calendar months.
Also what is the first month of the year - January or July