Solved: Diffrentiating same week numbers , but of different years.

[sebas1102]

Hi all,
I have production schedule which states the materials required for production, and the production start date. The dates range from the past 6months to the future 6months ie dec 05 to nov 06.
I need to consolidate the materials needed according to the production week number.
The week numbers for the consolidation table straches from column 2 to column 54. The material list stretches from A1 to K1.
The consolidation seems alright until the same months of different years pops out.
Let's say i have a production start date at 3-jan-05 that requires materials AA, BB and CC.
And I have another production start date at 5th-jan-06 that requires materials AA, BB and CC also.

Although they are of different production dates, but vba cannot recognise it since they have the same week number when using weeknum(). I will eventually have inaccurate data, as I have added up the material quantities of diffrent production dates.

How do I solve this problem?

Currently, i am using WEEKISO function:
[vba]
Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Int((Dat - lnDat + Weekday(lnDat) + 5) / 7)
End Function[/vba]

and [vba] For iRowStkdescWklyBrkDwn = 3 To LRowStkdescWklyBrkDwn
iItem = Cells(iRowStkdescWklyBrkDwn, StkDescWklyBrkDwn)
For iRowWkRangeWklyBrkDwn = 3 To LrowWkRangeWklyBrkDwn
iWeek = Cells(iRowWkRangeWklyBrkDwn, WkRangeWklyBrkDwn)
Windows("materialForecastNew.xls").Activate
Sheets("Sheet1").Activate
LRowMatlFCN = Cells(Rows.Count, StkDescMatlFC).End(xlUp).Row
Sum = 0
For iRowMatlFCN = 3 To LRowMatlFCN
If UCase(iItem) = Cells(iRowMatlFCN, StkDescMatlFC) And _
iWeek = Cells(iRowMatlFCN, WkNMatlFC) Then
Sum = Sum + Cells(iRowMatlFCN, lQtyMatlFC)
End If
Next iRowMatlFCN
[/vba]

[mdmackillop]

I've not studied your code, but can you not add a year value to the week to ensure a non recurring value. 2006 would produce figures from 601 to 652, 2007 from 701 to 752 etc. Easy to strip off the years again, if the week values only are required.
Regards
MD

[sebas1102]

oh! but how do I do that? do i change something from the weekiso formula?

[mdmackillop]

The following will change WEEKISO to produce the amended result. I don't see how the WEEKISO value is used elsewhere, so be sure and check for unintended results.
Regards
MD
[vba]Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long, Yr As Long
Yr = 100 * (Year(Dat) - 2000)
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Yr + (Int((Dat - lnDat + Weekday(lnDat) + 5) / 7))
End Function
[/vba]

[Shazam]

How about a formula?

=INT((A2-SUM(MOD(DATE(YEAR(A2-MOD(A2-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

[sebas1102]

The following will change WEEKISO to produce the amended result. I don't see how the WEEKISO value is used elsewhere, so be sure and check for unintended results.
Regards
MD


VBA:

Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long, Yr As Long
Yr = 100 * (Year(Dat) - 2000)
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Yr + (Int((Dat - lnDat + Weekday(lnDat) + 5) / 7))
End Function

omg thanks mdmackillop!!! you helped me cleared my all time headache!

oh, i used recording for the other part with weeknum bcos i didnt know how to use weekiso then, and forgot to change it back. thanks for pointing that out!
now its just a simple[vba] LRowProdnSDateMatlFC = Cells(2, ProdnSDateMatlFC).End(xlDown).Row
For irowmatlfc = 3 To LRowProdnSDateMatlFC
ddate = Cells(irowmatlfc, ProdnSDateMatlFC)
Cells(irowmatlfc, WkNMatlFC) = WEEKISO(ddate)
Next irowmatlfc
[/vba]!