Entering month/year, instead of month/day/year, and displaying as such

**Gunther Maplethorpe** · 03-09-2011, 02:23 PM

Is there a way to enter, say, "3/11" and have Excel recognize it as "March 2011" instead of assuming that I'm entering March 3rd of the current year? I've tried fiddling with the cell formatting, but to no avail. The best I can come up with is entering "3/(any day)/11" to display as month/year, but I'd like to NOT enter any day at all.

I realize that entries such as "3/11" are inherently ambiguous...is it March 11th, or March of 2011? But if I have a set of entries that will always be month/year, how can I force them to display as such?

Also acceptable would be forcing the cell to the last day of that given month (eg forcing "3/11" to display as "March 30th, 2011"), but I'd prefer to just have month/year without having to enter month/day/year.

EDIT: After realizing that this doesn;t explain things fully, I should state that I want an entry of 3/11 to display as March 2011, and an entry of 3/12 to display as March 2012.

**wonderdunder** · 03-09-2011, 02:34 PM

It's not exactly what you want but it may help - it will display the first three characters of the month and the year in full

Highlight the cell with the date in it.

Then click Format>Cells and select the "number" tab.

Then select "Custom" at the bottom of the list.

On the right, you will see a text box with "general" in it.

Replace this with the text MMM-YYYY

Click OK.

This will display the first 3 characters of the month and the year

**wonderdunder** · 03-09-2011, 02:39 PM

actually - type "MMMM YYYY" - that seems to give the full month and year (with no dash).

**Gunther Maplethorpe** · 03-09-2011, 02:43 PM

Yes, that's one of the things I've tried...but, still, an entry of "3/11" will assume I mean "March 11th" of the current year. The display will still be "Mar-2011", but when I take it a step further, an entry of "3/12", which I would like to mean "March 2012", will be assumed to mean "March 12, 2011", and thus still display "March 2011".

(I probably should have used this example above, instead of 3/11....

**wonderdunder** · 03-09-2011, 02:50 PM

Could you just enter the full year instead of the last two digits? Sorry if that seems daft - just trying to help (this is probably the first question that I've had the remotest idea of how to answer!). Other than that, I can only think of doing it by some long winded way (Vlookups or VBA).

**Gunther Maplethorpe** · 03-09-2011, 02:59 PM

Yes I *can* enter the four-digit year - and that does work - but I'd prefer not to...a couple extra keystrokes really adds up over time.

(Does that make me sound whiny?)

**ChemistB** · 03-09-2011, 04:22 PM

The only way would be with VBA. Here's some code which supposed date is in column B (adjust accordingly)

Please Login or Register  to view this content.

Will that work for you?

**Gunther Maplethorpe** · 03-09-2011, 04:28 PM

Not being terribly familiar with VBA, do I correctly assume that the Range("B:B") part should be my target for those cells I wish to be formatted? And that those would be the only ones affected?

**ChemistB** · 03-09-2011, 04:34 PM

Yes, so if your dates are in column A, change that to "A:A", if they would be in A, B or c, then "A:C" or even "A1:C20".
Right click on the spreadsheet tab and "View Code" and then double click on the sheet name in the VBA Explorer and paste the code in the text window on the right.
Then close that window (no need to save) and try your worksheet.

Edit: Added example workbook

**Gunther Maplethorpe** · 03-09-2011, 07:45 PM

Another fine solution from these forums...I'm so glad I've joined!

**CILLA** · 07-17-2011, 07:31 PM

Hi guys, I had the same problem. I changed the "B:B" to "E:E" and followed directions to the letter. I'm using excel 2010 and when I changed a date, it came up with an error. When I clicked 'debug', this line was highlighted. Target.Value = DateValue(NewMonth & "/" & 1 & "/" & NewYear)
Is there anything that I'm doing wrong that you can think of?

Entering month/year, instead of month/day/year, and displaying as such

LinkBack

Thread Tools

Rate This Thread

Display

Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Re: Entering month/year, instead of month/day/year, and displaying as such

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions