Counting no of days of a specific range of days from a list

[Manikandan]

I have a date range from month first to end of month. I want to count no of
entries from that list which range from 10th to 15th. For eg. a list starting
from 01.11.2009 to 30.11.2009. I want to count no of entries of date range
from 15.11.2009 to 20.11.2009.
Please help me....

 

[Ms-Exl-Learner]

Assume that you are having the Dates in A Column and do you want to get the
number of days between 15.11.2009 to 20.11.2009.

=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))

In your example the dates are entered with Full stop (.) instead of / or -
so I think it will not treated as dates. So replace the Full Stops to / or -
for converting it into Dates.

Remember to Click Yes, if this post helps!

 

[Manikandan]

it's not working for a range of cells... please send any other alterations.

 

[Ms-Exl-Learner]

I am sure that the Countif function will work on range of cells. Check
format of the dates. Have you converted the dates from 01.11.2009 to
01/11/2009 or 01-11-2009?

Remember to Click Yes, if this post helps!

 

[David Biddulph]

I assume that the VALUE(TEXT(DAY(...)) construct is a little Christmas joke?

Instead of
=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))you can use just =COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20)) though you may need to format the result as General or Number if Exceldecides to format it as a date.The difference is that if the answer is more than 31 my formula will stillwork, but yours won't.--David Biddulph"Ms-Exl-Learner" <[email protected]> wrote in messageAssume that you are having the Dates in A Column and do you want to getthe> number of days between 15.11.2009 to 20.11.2009.>>=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))>> In your example the dates are entered with Full stop (.) instead of / or -> so I think it will not treated as dates. So replace the Full Stops to /or -> for converting it into Dates.>> Remember to Click Yes, if this post helps!>> --------------------> (Ms-Exl-Learner)> -------------------->>> "Manikandan" wrote:>>> I have a date range from month first to end of month. I want to count noof>> entries from that list which range from 10th to 15th. For eg. a liststarting>> from 01.11.2009 to 30.11.2009. I want to count no of entries of daterange>> from 15.11.2009 to 20.11.2009.>> Please help me....

 

[Ms-Exl-Learner]

I don’t know why you are saying like this…
But while posting the post I know that this will be criticized.
If I use the countif formula like the below
=COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))
Then it’s returning the answer in date format like this 6-Jan-00. Then I
need to format the cell as General for making the result to display as 6.
Even though the cell was already formatted as General if I press F2 and give
enter then the cell result changed into 6-Jan-00 instead of 6. Again I need
to change the cell format as General, so any other solution is there for this
problem David Sir?

Anyway It’s very happy to me that I have made some persons to laugh and say
it is a Christmas Joke…

Anyway I am very happy to know that unknowingly I have offered a Christmas
Joke to some persons and made them to laugh…

--------------------
(Ms-Exl-Learner)
--------------------

I assume that the VALUE(TEXT(DAY(...)) construct is a little Christmas joke?

Instead of
=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))you can use just =COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20)) though you may need to format the result as General or Number if Exceldecides to format it as a date.The difference is that if the answer is more than 31 my formula will stillwork, but yours won't.--David Biddulph"Ms-Exl-Learner" <[email protected]> wrote in messagenews:[email protected]...> Assume that you are having the Dates in A Column and do you want to getthe> number of days between 15.11.2009 to 20.11.2009.>>=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))>> In your example the dates are entered with Full stop (.) instead of / or -> so I think it will not treated as dates. So replace the Full Stops to /or -> for converting it into Dates.>> Remember to Click Yes, if this post helps!>>

--------------------> (Ms-Exl-Learner)> -------------------->>> "Manikandan" wrote:>>> I have a date range from month first to end of month. I want to count noof>> entries from that list which range from 10th to 15th. For eg. a liststarting>> from 01.11.2009 to 30.11.2009. I want to count no of entries of daterange>> from 15.11.2009 to 20.11.2009.>> Please help me....

 

[Ms-Exl-Learner]

Oh! I read only the part of your post, because your post is showing in MSDN
Website as unformatted text except the first line. But now I understood that
the formula I have suggested will not give the correct answer when the count
goes beyond 31. Thanks for your guidance David Sir….

--------------------
(Ms-Exl-Learner)
--------------------

I assume that the VALUE(TEXT(DAY(...)) construct is a little Christmas joke?

Instead of
=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))you can use just =COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20)) though you may need to format the result as General or Number if Exceldecides to format it as a date.The difference is that if the answer is more than 31 my formula will stillwork, but yours won't.--David Biddulph"Ms-Exl-Learner" <[email protected]> wrote in messagenews:[email protected]...> Assume that you are having the Dates in A Column and do you want to getthe> number of days between 15.11.2009 to 20.11.2009.>>=VALUE(TEXT(DAY(COUNTIF(A:A,">"&DATE(2009,11,14))-COUNTIF(A:A,">"&DATE(2009,11,20))),"#########"))>> In your example the dates are entered with Full stop (.) instead of / or -> so I think it will not treated as dates. So replace the Full Stops to /or -> for converting it into Dates.>> Remember to Click Yes, if this post helps!>>

--------------------> (Ms-Exl-Learner)> -------------------->>> "Manikandan" wrote:>>> I have a date range from month first to end of month. I want to count noof>> entries from that list which range from 10th to 15th. For eg. a liststarting>> from 01.11.2009 to 30.11.2009. I want to count no of entries of daterange>> from 15.11.2009 to 20.11.2009.>> Please help me....

 

[David Biddulph]

Yes, I don't know why from time to time my posts get misformatted. I have
asked in the outlook express newsgroup, but with no luck.

 

[מיכ×ל (מיקי) ×בידן]

If U R using "Excel 2007/2010" - this might be the shortest formula:
=SUMPRODUCT((A:A>DATE(2009,11,14))*(A:A<DATE(2009,11,21)))
Micky

 

[מיכ×ל (מיקי) ×בידן]

Or even shorter:
=SUM((A:A>DATE(2009,11,14))*(A:A<DATE(2009,11,21)))
Pls note:
This is an Array-Formula - to be entered by pressing: CTRL+SHIFT+ENTER.
Micky

 

[Ron Rosenfeld]

I have a date range from month first to end of month. I want to count no of
entries from that list which range from 10th to 15th. For eg. a list starting
from 01.11.2009 to 30.11.2009. I want to count no of entries of date range
from 15.11.2009 to 20.11.2009.
Please help me....

Are your dates "real dates" formatted to look like above, or are they text
entries?

--ron