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

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!

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


"Manikandan" wrote:

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

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

"Ms-Exl-Learner" wrote:

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!

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


"Manikandan" wrote:

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

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!

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


"Manikandan" wrote:

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

"Ms-Exl-Learner" wrote:

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!

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


"Manikandan" wrote:

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

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))),"#########"))yo u 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" wrote in ... 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....

I dont 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 its 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 Its 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)
--------------------


"David Biddulph" wrote:

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))),"#########"))yo u 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" wrote in ... 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....

.

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


"David Biddulph" wrote:

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))),"#########"))yo u 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" wrote in ... 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....

.

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

"Ms-Exl-Learner" wrote in message
...
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)
--------------------


"David Biddulph" wrote:

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))),"#########"))yo u
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" wrote in
... 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....

.

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



"Manikandan" wrote:

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

Or even shorter:
=SUM((A:ADATE(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


"מיכ�ל (מיקי) �בידן" wrote:

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



"Manikandan" wrote:

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

On Wed, 23 Dec 2009 21:35:01 -0800, Manikandan
wrote:

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