Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon to
Sun then that should be 7 days....so why does Excel insist I use Mon to Mon
when in fact that is 8 days. How do I get around this problem. (I have to
have the weekday end on a sunday and not a monday).

hope you can help

Try this:

=INT(B1-A1+1)/7)

Hope this helps.

Pete

On Nov 30, 3:58 pm, Burt wrote:
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon to
Sun then that should be 7 days....so why does Excel insist I use Mon to Mon
when in fact that is 8 days. How do I get around this problem. (I have to
have the weekday end on a sunday and not a monday).

hope you can help

Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help

Nice explanation, Stephen.

Pete

On Nov 30, 4:36 pm, "Stephen" <none wrote:
Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message

...



Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help- Hide quoted text -

- Show quoted text -

<Nice explanation, Stephen.

I agree!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Pete_UK" wrote in message ...
| Nice explanation, Stephen.
|
| Pete
|
| On Nov 30, 4:36 pm, "Stephen" <none wrote:
| Then you will have to add 1 to your subtraction. It isn't Excel insisting on
| anything - it's how arithmetic works! If I give out tickets numbered
| sequentially, starting with number 7 and finishing with 27, how many tickets
| have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
| number of tickets, I need to use (last - first + 1). This example directly
| correlates with yours - if the number on each ticket is the day of the
| month.
|
| "Burt" wrote in message
|
| ...
|
|
|
| Hi,
|
| I want to be able to calculate the amount of weeks between 2 dates.
|
| If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
| can
| do:
|
| =(b1-a1)/7
|
| and it gives me the correct answer - 3 weeks (providing I re-format the
| answer).
|
| However, what my spreadsheet requires is that 1 week should be Monday to
| Sunday, and not Monday to Monday. If I count the amount of days from Mon
| to
| Sun then that should be 7 days....so why does Excel insist I use Mon to
| Mon
| when in fact that is 8 days. How do I get around this problem. (I have
| to
| have the weekday end on a sunday and not a monday).
|
| hope you can help- Hide quoted text -
|
| - Show quoted text -
|

Thanks for the explanation....but it still doesnt work.

If I place 23/12/07 (sunday) in a2 and todays date (03/12/07) in a1, I want
to be able to calculate the amount of weeks in between.

if i do =INT(A2-A1+1)/7) and then remove the dfate format, i get the answer 20

Any ideas?



"Stephen" wrote:

Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help

I missed a bracket in the formula I gave you earlier - try this:

=INT((A1-B1+1)/7)

Format cell as General.

Hope this helps.

Pete

On Dec 3, 10:57 am, Burt wrote:
Thanks for the explanation....but it still doesnt work.

If I place 23/12/07 (sunday) in a2 and todays date (03/12/07) in a1, I want
to be able to calculate the amount of weeks in between.

if i do =INT(A2-A1+1)/7) and then remove the dfate format, i get the answer 20

Any ideas?



"Stephen" wrote:
Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help- Hide quoted text -

- Show quoted text -

I think you have done something wrong then! When I do this I get the answer
3, which is the correct number of weeks. Are you sure you have entered the
data and formula correctly? (I notice that there are three typos in your
last message; perhaps you should slow down and improve accuracy.) If you
still can't find the problem, start with a new workbook (no prior
formatting) and type in the two dates and the formula. What do you get then?

"Burt" wrote in message
...
Thanks for the explanation....but it still doesnt work.

If I place 23/12/07 (sunday) in a2 and todays date (03/12/07) in a1, I
want
to be able to calculate the amount of weeks in between.

if i do =INT(A2-A1+1)/7) and then remove the dfate format, i get the
answer 20

Any ideas?



"Stephen" wrote:

Then you will have to add 1 to your subtraction. It isn't Excel insisting
on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many
tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example
directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday
to
Sunday, and not Monday to Monday. If I count the amount of days from
Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I
have
to
have the weekday end on a sunday and not a monday).

hope you can help

works a treat, thank you very much!

"Pete_UK" wrote:

I missed a bracket in the formula I gave you earlier - try this:

=INT((A1-B1+1)/7)

Format cell as General.

Hope this helps.

Pete

On Dec 3, 10:57 am, Burt wrote:
Thanks for the explanation....but it still doesnt work.

If I place 23/12/07 (sunday) in a2 and todays date (03/12/07) in a1, I want
to be able to calculate the amount of weeks in between.

if i do =INT(A2-A1+1)/7) and then remove the dfate format, i get the answer 20

Any ideas?



"Stephen" wrote:
Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help- Hide quoted text -

- Show quoted text -

You're welcome - thanks for feeding back. (have been away a couple of
days)

Pete

On Dec 3, 11:48 am, Burt wrote:
works a treat, thank you very much!



"Pete_UK" wrote:
I missed a bracket in the formula I gave you earlier - try this:

=INT((A1-B1+1)/7)

Format cell as General.

Hope this helps.

Pete

On Dec 3, 10:57 am, Burt wrote:
Thanks for the explanation....but it still doesnt work.

If I place 23/12/07 (sunday) in a2 and todays date (03/12/07) in a1, I want
to be able to calculate the amount of weeks in between.

if i do =INT(A2-A1+1)/7) and then remove the dfate format, i get the answer 20

Any ideas?

"Stephen" wrote:
Then you will have to add 1 to your subtraction. It isn't Excel insisting on
anything - it's how arithmetic works! If I give out tickets numbered
sequentially, starting with number 7 and finishing with 27, how many tickets
have I given out? Twenty one. But 27 - 7 = 20. To correctly calculate the
number of tickets, I need to use (last - first + 1). This example directly
correlates with yours - if the number on each ticket is the day of the
month.

"Burt" wrote in message
...
Hi,

I want to be able to calculate the amount of weeks between 2 dates.

If in A1 I have 07/01/08 (mon) and in B1 I have 28/01/08 (mon) I know I
can
do:

=(b1-a1)/7

and it gives me the correct answer - 3 weeks (providing I re-format the
answer).

However, what my spreadsheet requires is that 1 week should be Monday to
Sunday, and not Monday to Monday. If I count the amount of days from Mon
to
Sun then that should be 7 days....so why does Excel insist I use Mon to
Mon
when in fact that is 8 days. How do I get around this problem. (I have
to
have the weekday end on a sunday and not a monday).

hope you can help- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -