Any Date...

[Paul]

Hi Guys,

Can anyone help me with this one?...

How do I get Excel to recognise 'any' date?

For example:

If a column of cells can contain any date between
01/01/2004 and 31/12/2006, how (in the next column) do I
get it to list all the dates in February as a 2, March as
a 3, and so on - so that they can be totalled...?!

Alternatively, how (at the bottom maybe) do I count all
of the dates entered for each month?

i.e.: A
01/02/2004
01/02/2004
13/02/2004
01/03/2004
05/03/2004
15/03/2004
15/03/2004
Total Feb 3 (??)
Total Mar 4 (??)

If anyone can help, it will be SO appreciated! Been at
this for 2 days now!?!...

Thank you in advance...

 

[Guest]

Hi,
you could try this:
in the next column to that where you have your dates write
a formula like this one:
formula 1: "=month(<cell reference>)
formula 2: "=countif(B1:B7; 2)"
formula 3: "=countif(B1:B7; 3)"
i.e.: A B
1 01/02/2004 "=month(A1)"
2 01/02/2004 "=month(A2)"
3 13/02/2004 "=month(A3)"
4 01/03/2004 "=month(A4)"
5 05/03/2004 "=month(A5)"
6 15/03/2004 "=month(A6)"
7 15/03/2004 "=month(A7)"
8 Total Feb 3 (??) <- here you put the formula 2
9 Total Mar 4 (??) <- here you put the formula 3

Hope will help you.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2004))

 

[Bob Phillips]

For Feb

=SUMPRODUCT(--(MONTH(A1:A1000)=2))

or

=SUMPRODUCT(--(TEXT(A1:A1000,"mmm")="Feb"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)