# Month Formula

#### FinancialAnalystKid

##### Well-known Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Barrie Davidson

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

=TEXT(A1,"mmm yyyy")

Regards,

#### Seti

##### Well-known Member
Maybe =TEXT(A1,"MMM YYYY") ?

#### texasalynn

##### Well-known Member
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

##### MrExcel MVP
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

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

Regards
Eric

#### Guitarde

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
Here is an example:
Book3
ABCDEF
11datecountryqty
1212/1/2004usa20
1325/2/2004can10
1420/8/2004usa30
1512/1/2005mex200
16
17TOTAL260
18
19
20SALESBYYEARcanusamextotal
2120041/1/20041050060
2220051/1/200500200200
2320061/1/20060000
240
251050200260
26
Sheet1

Replies
1
Views
161
Replies
5
Views
302
Replies
3
Views
172
Replies
5
Views
380
Replies
1
Views
177

1,195,972
Messages
6,012,622
Members
441,715
Latest member
TTP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back