PDA

View Full Version : Getting Week Numbers to Display over Multiple Years without Resetting to 1



Panda
06-16-2010, 06:00 AM
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
06-16-2010, 08:50 AM
Enter in C5 and copy down
=CEILING((B5-40179)/7,1)+1

Panda
06-17-2010, 12:32 AM
Thank you so much for this, it has worked a treat :rotlaugh: Makes production planning a lot easier to explain now lol

Panda
06-17-2010, 03:21 AM
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
06-17-2010, 04:50 AM
40179 is the number value of 1 Jan 2010. See Help for the Ceiling function.

Panda
06-17-2010, 02:42 PM
Cheers I understand the logic now...thanks again for your help :yes

DebFarris
01-10-2011, 02:48 PM
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.

xld
01-10-2011, 03:50 PM
Put this in B3 and copy down

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

DebFarris
01-10-2011, 04:02 PM
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
01-10-2011, 04:44 PM
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

xld
01-10-2011, 04:53 PM
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
12-31-2015, 06:19 AM
Posting so I can view the attachment