Date Month Syntax

[walkingfather]

What am I doing wrong with this array formula?

{=SUM(B3:B20=MONTH(1))*(C3:C20="income")*(F3:F20+G3:G20)}

The =month(1) i want it to find all enteries in Feb.

Thanks for your help

Gavin

 

[PaddyD]

excel stores dates as integers, starting with 1 for 01/01/1900, 2 for 02/01/1900 etc. Today, 5th December 2005 is 38,691

The month of 01/01/1900 is 1.

The syntax for that sort of thing is:

sum((range=condition)*(range=condition)*(range_to_sum))

...notice the brackets.

 

[walkingfather]

I understand the syntax for the sum, and i understand how excel stores dates from 1900.

but i just must be stupid on the date condition

sum((range=condition) what is the formula syntax for finding dates between 1/1/06 and 1/31/06 ??...

Do i have to use and if statement?

my dates are stored in the range with this format mm/dd/yyyy

thanks,
gavin

 

[PaddyD]

some examples:

Book2
	A	B	C	D	E	F
1	Date	Amount
2	1/12/2005	1		21
3	15/12/2005	2		21
4	29/12/2005	3		21
5	12/01/2006	4		21
6	26/01/2006	5		21
7	9/02/2006	6		9		FALSE
8	23/02/2006	7		9
9	9/03/2006	8		9
10	23/03/2006	9				FALSE
11	6/04/2006	10
12	20/04/2006	11
13	1/01/2001	12
Sheet2

 

[walkingfather]

Something isn't right with column D.
Your first formula should yeild 13 instead of 21

0=Jan
1=Feb

in the feb dates you have 6+7

Maybe it's the way the dates are arranged?

you have dd/mm/yyyy instead of mm/dd/yyyy

i can't get it to work either way

thanks for the help though i feel i am getting closer every day,

Gavin

 

[PaddyD]

"Your first formula should yeild 13 instead of 21

0=Jan
1=Feb"

The month number for January is 1, for February 2 etc. There is no month whose number is 0, at leasst not in terms of the excel defaults.

 

[walkingfather]

Hey thanks for the info...I used one of your formulas and tweaked it a little bit to fit my sheet...thanks again for all your time and help,

Gavin