Month Formula

[FinancialAnalystKid]

I'm brain dead today. It's raining and it's friday and I have a SIX day weekend coming up so help me! Just 6 more hours of work to go... LOL

Two issues:

Issue 1.
I have dates in my sheet. 1/1/2004 format. I need another a formula to point to it and give me month and year like Jan 2004.

*basically I'm just pointing to it but I want it to omit DAY from it's 'memory' if that makes sense.

This cell is being referenced in another sheet with a sumproduct formula to gather all infor for that month and year.

Issue B.
Will I be able to use the sumproduct if it points to the formula with the result or will I have to change the results to text? The reason is the column of months are labeled Jan 2004 in text format. Hence my point "*" above.

 

[Barrie Davidson]

If I'm understanding your requirement, would this work?

=TEXT(A1,"mmm yyyy")

Regards,

 

[Seti]

Maybe =TEXT(A1,"MMM YYYY") ?

 

[texasalynn]

Can you give a little detail. Not understanding what you mean about the sumproduct issue?

Also you create formula to the date
=+A1
then format it for just the month year

texasalynn

 

[Zack Barresse]

ADVERTISEMENT

Remember, it's a date. This makes it a number to Excel, which can take any variety of shapes/sizes (formats). If you point to that cell, it will be pointing at a number, not text. You'd have to use the TEXT function (as Barrie/Seti points out) to look at just the Text.

It's much like looking at dates in VBA, if you want to look at the cell text you must use the .Text method instead of the .Value method. Make sense?

 

[Guitarde]

Sumproduct will work if you add other columns for Year & Month, no need to convert these to text.

Regards
Eric

 

[Guitarde]

ADVERTISEMENT

Other option if you want to go out for your week end now.

January 1st 2004 = 37987
January 1st 2005 = 38353

so you can just use these values into your sumproduct formula
=sumproduct((date>=37987)*(date<38353)*(qty)) 'This would add all 2004 qty


If you want totals by month them just change the min & max dates into the formula, in that case you just put these dates into cells & keep the same formula across your spreadsheet.

So at the end the conclusion is that you don't need new columns with Month & Year.

As far showing the date like Jan 2004 this can be done with cell formatting, again no need to add another column.

 

[TTom]

If you need a fomula, not in marco, I use this...
Assumes your full date is in cell A1

=CONCATENATE(MONTH(A1),"/",YEAR(A1))

TTom

 

[Guitarde]

Here is an example:

Book3
	A	B	C	D	E	F
11	date	country	qty
12	12/1/2004	usa	20
13	25/2/2004	can	10
14	20/8/2004	usa	30
15	12/1/2005	mex	200
16
17	TOTAL		260
18
19
20	SALESBYYEAR		can	usa	mex	total
21	2004	1/1/2004	10	50	0	60
22	2005	1/1/2005	0	0	200	200
23	2006	1/1/2006	0	0	0	0
24						0
25			10	50	200	260
26
Sheet1