Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel-Allowing for multiple date formats in one column

Oct 6, 2005 9:50PM PDT

Excel 2003 on XP SP2

I have a column of dates that I want formatted as
MMMM YYYY. However, sometimes I may only have a year, but if I only enter a year in the cell, I get a date of June (or some other month) 1905. Is there anyway I can tell excel to look at years as just years (YYYY), and not convert them, but at the same time if the user inputs a MM/YY to show it as Mmmm, YYYY.

Thanks

Discussion is locked

- Collapse -
Re: dates in Excel
Oct 6, 2005 11:16PM PDT

The makers of Excel did some rather undocumented things with the input of dates:

- If you enter a number, it's interpreted as the number of days since 31-12-1899 (dd-mm-yyyy format for me, as I'm in Europe). So 1 -> 1-1-1900,
- If you enter something like 1/1, it's interpreted as a date in the the current year: 1-1 -> 1-1-2005
- If you enter the full date, like 1/1/2004, it's interpreted as 1-1-2004. So 1/1/2004 -> 1-1-2004.
- However, 1/1/1 isn't interpreted as 1-1-0001, but as 1-1-2001. The same way: 1/1/10 -> 1/1/2005, but 1-1-256 stays a text string.

So I'm afraid the answer to your question is NO.

The thing nearest to what you want is to format the column as right justified text, and let the user type in what he wants to see, like 'Jul 2005' and '2004'

Hope this helps.


Kees

- Collapse -
(NT) (NT) Thanks Kees for the iformative answer
Oct 7, 2005 3:03AM PDT