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
Enter in C5 and copy down
=CEILING((B5-40179)/7,1)+1
Last edited by Aussiebear; 04-11-2023 at 03:53 PM. Reason: Added code tags
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thank you so much for this, it has worked a treatMakes production planning a lot easier to explain now lol
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.
Thanks=CEILING((B5-40179)/7,1)+1
Last edited by Aussiebear; 04-11-2023 at 03:54 PM. Reason: Added code tags
40179 is the number value of 1 Jan 2010. See Help for the Ceiling function.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Cheers I understand the logic now...thanks again for your help
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.
Put this in B3 and copy down
=CEILING((A3-A$2)/7,1)+1
Last edited by Aussiebear; 04-11-2023 at 03:54 PM. Reason: Added code tags
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Try
in B2 and copy down=TRUNC(((A2-DATE(YEAR(A2),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A2),1,1))>WEEKDAY(A2),1,0)
Last edited by Aussiebear; 04-11-2023 at 03:55 PM. Reason: Added code tags
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
They weren't as I saw it. Does it matter as it takes the first date as its base.Originally Posted by DebFarris
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Posting so I can view the attachment
Posting Permissions
Reply With Quote
