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
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Apr 22, 2013 at 10:36 AM
Hello everyone,
I would like some help please.. :)

I would like to calculate the weeks between week numbers. i.e
Start Date: 04/03/2013 Week 10
End Date: 03/05/2013 Week 10

Weeks between these dates = 1 not 0 as its in the same week. Formula will show 0...
So in my formula i need it to say something like IF(START DATE - END DATE = 0, PUT 1, OTHERWISE DO ONE DATE MINUS THE OTHER DATE)

However, if the start date is example: 04/03/2013 and end date is: 18/03/2013 There are 3 weeks between them as the 18th falls on the 3rd week. But the formula im using will show 2 weeks.

Hope you can you help?

Many Thanks,

Ant

5 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Apr 16, 2013 at 11:37 AM
Hi A-Bomb,

Try this formula:
=ROUNDUP(((A2-A1)+1)/7,0)

Best regards,
Trowa
0
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013

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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
A-Bomb
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013

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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
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
0

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
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 :)
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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

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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
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
0