Help on..Calculating number of weeks between week numbers..

[Closed]
Report
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
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

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
Hi A-Bomb,

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

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

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Wit this work
=IF(OR(A1="",B1=""),"",IF(A1-B1=0,1,ROUNDUP((A1-B1)/7,0)))
a1=end dare
b1=start date
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013

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
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
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
Posts
4
Registration date
Wednesday April 10, 2013
Status
Member
Last seen
April 18, 2013

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 :)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
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