T
tsony
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)?
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
David Biddulph
=INT((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
C
CLR
=INT((A2-A1)/7)&" weeks "& ((A2-A1)/7-INT((A2-A1)/7))*7&" days"
Vaya con Dios,
Chuck, CABGx3
Vaya con Dios,
Chuck, CABGx3
T
tsony
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
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
D
David Biddulph
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?
you need someone to tell you how to add 1 to B2-A2?
T
tsony
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.
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.
T
tsony
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.
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.
R
Rick Rothstein \(MVP - VB\)
You have mistaken. In excel if we subtract two dates, it wont
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
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
R
Rick Rothstein \(MVP - VB\)
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
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
T
tsony
Rick,
100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.
Thanks
100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.
Thanks
R
Rick Rothstein \(MVP - VB\)
What is the last span of weeks to make 100% then?
Rick
Rick
T
tsony
Sorry Rick,,
It is 11 - 13 100%
Thanks
It is 11 - 13 100%
Thanks
Rick Rothstein (MVP - VB) said:What is the last span of weeks to make 100% then?
Rick
B
Bob Phillips
=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)
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
R
Rick Rothstein \(MVP - VB\)
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
=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
D
daddylonglegs
With start date in A2 and end date in B2 as suggested previously try
=MATCH((B2-A2+1)/7,{0,4,7,11})/4
=MATCH((B2-A2+1)/7,{0,4,7,11})/4