number of days in week issue.

[Burt]

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

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[Stephen]

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.

 

[Pete_UK]

Nice explanation, Stephen.

Pete

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.










- Show quoted text -

 

[Niek Otten]

<Nice explanation, Stephen.>

I agree!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.
| >
| >
| > | >
| >
| >
| > > 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 -
|

 

[Burt]

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?

 

[Pete_UK]

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

 

[Stephen]

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]

works a treat, thank you very much!

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

 

[Pete_UK]

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

Pete