Help on..Calculating number of weeks between week numbers..
Closed
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013
-
Apr 10, 2013 at 06:22 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 22, 2013 at 10:36 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 22, 2013 at 10:36 AM
Related:
- Help on..Calculating number of weeks between week numbers..
- Windows calendar show week number - Guide
- Does instagram delete message requests after 4 weeks - Guide
- Mac calendar show week number - Guide
- Show week number in google calendar android - Guide
- Calculating fuel surcharge - Excel Forum
5 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 16, 2013 at 11:37 AM
Apr 16, 2013 at 11:37 AM
Hi A-Bomb,
Try this formula:
=ROUNDUP(((A2-A1)+1)/7,0)
Best regards,
Trowa
Try this formula:
=ROUNDUP(((A2-A1)+1)/7,0)
Best regards,
Trowa
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013
Apr 17, 2013 at 09:57 AM
Apr 17, 2013 at 09:57 AM
Hi There,
Many thanks for your help however it still has not worked unfortunatly..
I need to work out how many weeks between dates / weeks. Example of the dates below. I need to work out how many weeks between the start billing date and the actual shipping date.
Billing Start Date 04/03/2013 Falls in Week 10
Actual Ship Date 03/18/2013 Falls in Week 12
The actual billing weeks between these dates is 3, as i need to count week 10 as 1, week 11 will be 2 weeks and week 12 will be 3 weeks total.
So we need to be charged for 3 weeks storage in total.
Would be great if you can help?
Thanks
Ant
Many thanks for your help however it still has not worked unfortunatly..
I need to work out how many weeks between dates / weeks. Example of the dates below. I need to work out how many weeks between the start billing date and the actual shipping date.
Billing Start Date 04/03/2013 Falls in Week 10
Actual Ship Date 03/18/2013 Falls in Week 12
The actual billing weeks between these dates is 3, as i need to count week 10 as 1, week 11 will be 2 weeks and week 12 will be 3 weeks total.
So we need to be charged for 3 weeks storage in total.
Would be great if you can help?
Thanks
Ant
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 18, 2013 at 05:32 AM
Apr 18, 2013 at 05:32 AM
Wit this work
=IF(OR(A1="",B1=""),"",IF(A1-B1=0,1,ROUNDUP((A1-B1)/7,0)))
a1=end dare
b1=start date
=IF(OR(A1="",B1=""),"",IF(A1-B1=0,1,ROUNDUP((A1-B1)/7,0)))
a1=end dare
b1=start date
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013
Apr 18, 2013 at 06:55 AM
Apr 18, 2013 at 06:55 AM
Hi rizvisa1,
Thanks for your response, however it still does not seem to work..
Ill try and explain better..
Cell M2 contains the Billing Start Date week : 10
Then we have the actual ship week starting in cell L19
Ship Week
10
11
12
13
So i need a column next to the actual ship week titled 'Actual Billing weeks'
This needs to calculate the weeks between cell M2 and cell L19.
BUT if M2 is the same week number i.e week 10, as the L19 this must equal 1 week not 0.
So if M2 =10 and L19 = 11, the answer needs to be 2. As there are 2 weeks between them not 1... Week 10 is 1, and week 11 is another week..
Im not sure if im making sense?? Hope so, thanks again for your continued support on this,
Many Thanks
Ant
Thanks for your response, however it still does not seem to work..
Ill try and explain better..
Cell M2 contains the Billing Start Date week : 10
Then we have the actual ship week starting in cell L19
Ship Week
10
11
12
13
So i need a column next to the actual ship week titled 'Actual Billing weeks'
This needs to calculate the weeks between cell M2 and cell L19.
BUT if M2 is the same week number i.e week 10, as the L19 this must equal 1 week not 0.
So if M2 =10 and L19 = 11, the answer needs to be 2. As there are 2 weeks between them not 1... Week 10 is 1, and week 11 is another week..
Im not sure if im making sense?? Hope so, thanks again for your continued support on this,
Many Thanks
Ant
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 18, 2013 at 10:22 AM
Apr 18, 2013 at 10:22 AM
Hi Ant,
The last 0 should be 1.
In combination with rizvisa's formula:
=IF(OR(A1="",A2=""),"",IF(A2-A1=0,1,ROUNDUP((A2-A1+1)/7,1)))
With
A1: 04/03/2013
A2: 18/03/2013
And formula cell formatted as number result is 3.
With what dates are you getting the wrong result?
Best regards,
Trowa
The last 0 should be 1.
In combination with rizvisa's formula:
=IF(OR(A1="",A2=""),"",IF(A2-A1=0,1,ROUNDUP((A2-A1+1)/7,1)))
With
A1: 04/03/2013
A2: 18/03/2013
And formula cell formatted as number result is 3.
With what dates are you getting the wrong result?
Best regards,
Trowa
Didn't find the answer you are looking for?
Ask a question
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013
Apr 18, 2013 at 11:08 AM
Apr 18, 2013 at 11:08 AM
Hello Trowa,
I have pasted below what im working on, I have also added the cell refernces as i still cant seem to make it work
Week
Storage start Date 04/03/2013 10 Cell D4 Cell E4
Cell Ref Ship Date (C7) Ship Week (D7) Bilable Weeks (E7)
B8 03/05/2013 10
B9 03/05/2013 10
B10 03/11/2013 11
B11 03/11/2013 11
B12 03/18/2013 12
B13 03/18/2013 12
B14 03/18/2013 12
B15 03/18/2013 12
Column E needs to contain the formula.. So can you confirm in your formula what cell references i need to use as i have added the cell refernces i am using...
or can i upload my document so you can insert the formula?
Any help would be great, thanks very much for your help.
Thanks
Many Thanks :)
I have pasted below what im working on, I have also added the cell refernces as i still cant seem to make it work
Week
Storage start Date 04/03/2013 10 Cell D4 Cell E4
Cell Ref Ship Date (C7) Ship Week (D7) Bilable Weeks (E7)
B8 03/05/2013 10
B9 03/05/2013 10
B10 03/11/2013 11
B11 03/11/2013 11
B12 03/18/2013 12
B13 03/18/2013 12
B14 03/18/2013 12
B15 03/18/2013 12
Column E needs to contain the formula.. So can you confirm in your formula what cell references i need to use as i have added the cell refernces i am using...
or can i upload my document so you can insert the formula?
Any help would be great, thanks very much for your help.
Thanks
Many Thanks :)
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 20, 2013 at 07:44 AM
Apr 20, 2013 at 07:44 AM
May be posting a book with some example would help all of use.
From what you posted, I read as follow
1. D4: Start Date 04/03/2013
2. E4 : week number 10
3 Cx : Ship Dates
4. Dx : Ship weeks
5. Ex : billable weeks (to be found from formula)
This is how I understand your data
In E columns you have billable weeks as found by formula
=IF(OR(D$4="",C8="", C8<D$4),"",IF(C8-D$4=0,1,ROUNDUP((C8-D$4)/7,0)))
You should note that the formula is not using your "number of week" columns as it is not needed.
What is your expectation
From what you posted, I read as follow
1. D4: Start Date 04/03/2013
2. E4 : week number 10
3 Cx : Ship Dates
4. Dx : Ship weeks
5. Ex : billable weeks (to be found from formula)
This is how I understand your data
D4: 03/04/2013 E4: 10 C8: 03/05/2013 D8: 10 E8: 1 C9: 03/05/2013 D9: 10 E9: 1 C10: 03/11/2013 D10: 11 E10: 1 C11: 03/11/2013 D11: 11 E11: 1 C12: 03/18/2013 D12: 12 E12: 2 C13: 03/18/2013 D13: 12 E13: 2 C14: 03/18/2013 D14: 12 E14: 2 C15: 03/18/2013 D15: 12 E15: 2
In E columns you have billable weeks as found by formula
=IF(OR(D$4="",C8="", C8<D$4),"",IF(C8-D$4=0,1,ROUNDUP((C8-D$4)/7,0)))
You should note that the formula is not using your "number of week" columns as it is not needed.
What is your expectation
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 22, 2013 at 10:36 AM
Apr 22, 2013 at 10:36 AM
Hi Ant,
I think I finally understand you.
In the above post from Rizvisa E10 and E11 should be 2 and E12:E15 should be 3.
To achieve this use:
=(D8-$E$4)+1
Very curious to know if this is what you are looking for.
Best regards,
Trowa
I think I finally understand you.
In the above post from Rizvisa E10 and E11 should be 2 and E12:E15 should be 3.
To achieve this use:
=(D8-$E$4)+1
Very curious to know if this is what you are looking for.
Best regards,
Trowa
