I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.
Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).
Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).
I deal with about 100 to 150 of these in each spreadsheet.
I am using MS Excel 97
The solution depends, in part, on how you are defining the week number. Some
years can have 53 weeks. If you are
using the standard ISO definition
the year will always be in this century
then week/year entries are entered as numbers
then the following VBA routine should work.
To enter this routine, Tools/Macros/Visual Basic Editor should open the VB
Editor. Ensure your project is highlighted in the Project Explorer window,
then Insert/Module and paste the code below into the window that opens.
To use this function, enter the function
=WkSubtr(dt, NumWeeks)
into some cell where dt refers to your wk/yr construct representing the date,
and NumWeeks refers to the number of weeks you wish to subtract. These can be
either values or refer to cells containing the appropriate values.
As written, the routine does NOT do error checking for incompatible values.
If this helps, it can be easily modified to do error checking, and also to be
sensitive to dates outside of the 21st century.
=================================================
Option Explicit
Function WkSubtr(dt, NumWeeks) As Integer
Dim Dt1 As Date
Dim WknumFirstMonday As Integer
Dim Yr As Integer
Dim WkNum As Integer
Yr = 2000 + dt Mod 100
WkNum = Int(dt / 100)
'First Monday of year
Dt1 = DateSerial(Yr, 1, 1) + 7 - Weekday(DateSerial(Yr, 1, 1) + 5)
'ISOWeeknumber of First Monday
WknumFirstMonday = ISOWeeknum(Dt1)
'Adjust wknum for 53 week years
If WknumFirstMonday = 2 Then WkNum = WkNum - 1
'compute starting date
Dt1 = Dt1 + 7 * (WkNum - 1)
'subtract number of weeks
Dt1 = Dt1 - 7 * NumWeeks
'compute new weeknum/year
Yr = Year(Dt1) - 2000
WkNum = ISOWeeknum(Dt1)
WkSubtr = WkNum * 100 + Yr
End Function
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
=======================================
--ron
