Getting Week Numbers to Display over Multiple Years without Resetting to 1

[Panda]

Hi There,

I have a spreadsheet containing a list of part numbers, their due date, the due date in terms of Week Number, quantity due and how many weeks away from the current week they are from being due.

I am trying to get the week numbers to continue adding when the year moves from 2010 to 2011 so effectivly I have a part number 60 weeks away rather then -8 weeks away.

Attached is the spreadsheet I am trying to get this implemented for.

Can anyone help?

Thanks

[mdmackillop]

Enter in C5 and copy down

=CEILING((B5-40179)/7,1)+1

[Panda]

Thank you so much for this, it has worked a treat :rotlaugh: Makes production planning a lot easier to explain now lol

[Panda]

Now that I have got my spreadsheet working I was wondering if someone can explain to me what this formula is actually doing. why 40179? I assume the /7 is refering to the amount of days in a week, from then onwards I am stumped.


=CEILING((B5-40179)/7,1)+1

Thanks

[mdmackillop]

40179 is the number value of 1 Jan 2010. See Help for the Ceiling function.

[Panda]

Cheers I understand the logic now...thanks again for your help :yes

[DebFarris]

Hello MD- I have a similar situation, but I just want the week numbers to continue adding from 2010 to 2011 and beyond- without having a due date. Here is a sample of my spreadsheet.

[Bob Phillips]

Put this in B3 and copy down


=CEILING((A3-A$2)/7,1)+1

[DebFarris]

Thank you XLD... but what if the dates are not necessarily in sequential order, or in a set numerical fashion? Meaning that I might have this...

1/11/10
2/1/11
3/1/10
1/11/10
1/11/10
2/1/10

[Aussiebear]

Try

=TRUNC(((A2-DATE(YEAR(A2),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A2),1,1))>WEEKDAY(A2),1,0)
in B2 and copy down

[Bob Phillips]

Thank you XLD... but what if the dates are not necessarily in sequential order, or in a set numerical fashion? Meaning that I might have this...

1/11/10
2/1/11
3/1/10
1/11/10
1/11/10
2/1/10

They weren't as I saw it. Does it matter as it takes the first date as its base.

[Wolfpack123]

Posting so I can view the attachment