Date function
 

Is anybody knows how to deduct two dates and the result should be in number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

=INT((A2-A1)/7)&" weeks "& ((A2-A1)/7-INT((A2-A1)/7))*7&" days"

Vaya con Dios,
Chuck, CABGx3


"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

Dear David,

If we include the end date what formula we use for eg 01/01/08 to 21/01/08
number of days should be 21. if we deduct the two dates the result comes to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

You are saying you want to add 1 to the result of the B2-A2 subtraction. Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to 21/01/08
number of days should be 21. if we deduct the two dates the result comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

David,

You have mistaken. In excel if we subtract two dates, it wont include the
end date. I know how to add and subtract 1. Thanks for your reply.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2 subtraction. Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to 21/01/08
number of days should be 21. if we deduct the two dates the result comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if it is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2 subtraction. Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to 21/01/08
number of days should be 21. if we deduct the two dates the result comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

You have mistaken. In excel if we subtract two dates, it wont
include the end date.

I'm not sure what mistake you think David made, but what you are talking
about is not something confined to Excel and dates. And, actually, to be
technical about it, it is the start date that is not included. The opposite
of subtraction is addition... the value you get by subtracting a first
number form a second number is what has to be added to the first number in
order to get back to the second one again. This applies to all numbers, not
just Excel dates. For example, using your day numbers from the same month...
21-1=20, not 21. As David alluded to, if you need to include the start date
in the count of the difference in your days, you have to add 1 to your
subtraction result.

Rick

Date function
 

The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of the
other two shown or what is left over for the implied 11-12 final weeks span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2 subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

Rick,

100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of the
other two shown or what is left over for the implied 11-12 final weeks span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2 subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

What is the last span of weeks to make 100% then?

Rick


"tsony" wrote in message
...
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for
something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of
the
other two shown or what is left over for the implied 11-12 final weeks
span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if
it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2
subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be
in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

Sorry Rick,,

It is 11 - 13 100%

Thanks


"Rick Rothstein (MVP - VB)" wrote:

What is the last span of weeks to make 100% then?

Rick


"tsony" wrote in message
...
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for
something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of
the
other two shown or what is left over for the implied 11-12 final weeks
span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if
it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2
subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be
in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

=LOOKUP(INT((B2-A2+1)/7),{0,4,7,11},{0.25,0.5,0.75,1})*100

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"tsony" wrote in message
...
Sorry Rick,,

It is 11 - 13 100%

Thanks


"Rick Rothstein (MVP - VB)" wrote:

What is the last span of weeks to make 100% then?

Rick


"tsony" wrote in message
...
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it
is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me.
First,
there are 52 weeks in a year, not 100, unless the 100 stands for
something
else. Second, your week range percentages won't get anywhere near the
100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of
the
other two shown or what is left over for the implied 11-12 final weeks
span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old
one.
After finding the number of weeks there is another classification.
if
it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use
to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2
subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the
result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should
be
in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

I guess this will do what you want...

=IF(INT((B2-A2)/7)<4,0.25,IF(INT((B2-A2)/7)<7,0.5,IF(INT((B2-A2)/7)<11,0.75,IF(INT((B2-A2)/7)<14,1,NA()))))*100

Note, it produces a #N/A error if the number of weeks is 14 or greater. The
100 on the end is the number you specified; perhaps it would be better if
you put that value in a cell and put the cell reference in the formula
instead.

Rick


"tsony" wrote in message
...
Sorry Rick,,

It is 11 - 13 100%

Thanks


"Rick Rothstein (MVP - VB)" wrote:

What is the last span of weeks to make 100% then?

Rick


"tsony" wrote in message
...
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it
is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me.
First,
there are 52 weeks in a year, not 100, unless the 100 stands for
something
else. Second, your week range percentages won't get anywhere near the
100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of
the
other two shown or what is left over for the implied 11-12 final weeks
span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old
one.
After finding the number of weeks there is another classification.
if
it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use
to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2
subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the
result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should
be
in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?

Date function
 

With start date in A2 and end date in B2 as suggested previously try

=MATCH((B2-A2+1)/7,{0,4,7,11})/4

"tsony" wrote:

Sorry Rick,,

It is 11 - 13 100%

Thanks


"Rick Rothstein (MVP - VB)" wrote:

What is the last span of weeks to make 100% then?

Rick


"tsony" wrote in message
...
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.

Thanks


"Rick Rothstein (MVP - VB)" wrote:

The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for
something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of
the
other two shown or what is left over for the implied 11-12 final weeks
span.
Could you give more realistic numbers for what you are looking for?

Rick


"tsony" wrote in message
...
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if
it
is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to
find
the result.

"David Biddulph" wrote:

You are saying you want to add 1 to the result of the B2-A2
subtraction.
Do
you need someone to tell you how to add 1 to B2-A2?
--
David Biddulph

"tsony" wrote in message
...
Dear David,

If we include the end date what formula we use for eg 01/01/08 to
21/01/08
number of days should be 21. if we deduct the two dates the result
comes
to
20, please advice

"David Biddulph" wrote:

=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
--
David Biddulph

"tsony" wrote in message
...
Is anybody knows how to deduct two dates and the result should be
in
number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?