date as text (2003)

Topic

New Reply

When I enter in a cell ‘March 8’ it converts it to 8-Mar. (or any other month) I want ‘March 8’
I have tried changing the formating of the cell in a number of ways but can’t seem to make it stick for the next time I enter it.
I do not need to use the date in any calulations it is just a title.
Now if I enter ‘March 8th’ then it is ok. But I just want to enter March 8.
I think it has to do with the date as text, but I can’t get it without the th, st, rd, nd added to the end.
Is there a way to enter the date as text without it converting it?

Reply | Quote

Replies

Type an apostrophe in front of it. Like this : ‘March 8
This forces text entry on the date, and you will still be able to calculate with it if the time comes about!

Reply | Quote

Wow, that was easy!
Thanks for the fast reply!

Reply | Quote

You mistyped: If you enter it as text you will not be able to use it in calculations if the time comes. You would have to convert it from text to a date in some manner.

Steve

Reply | Quote

Hi Steve,

I tried it out. If I type ‘March 8 in A1 and ‘March 7 in A2 and then in A3 type =A1-A2, then it gives 1. Or =A1 + 10 and it gives 2008/03/18. So it must still see a date behind it??

Reply | Quote

It seems I am the one mistaken. XL seems to convert the text to a date if/when you do any math with it.

It seems to assume that the year is the current year when the formula gets calculated unless the text date explicitly lists it.

Thanks for pointing it out!

Steve

Reply | Quote

Excel seems to surprise one from time to time, no more that me!!
Just before I sent the reply, I tested it in Excel and was surprised myself that it calculated; hence the fact that I added it to my reply.

Reply | Quote

Hi Michael

If you click ‘format’, ‘cell’ and then select ‘number’ from the tabs and then Custom you can choose a ready made format for a date or one of your own, if for your example you made a custom format of mmmm-dd and typed in the cell 31/01/08 the cell would display January-31 and if you typed 01/12/08 it would show December-01

HTH

Steve

Reply | Quote

I agree with Steve Hocking: you can enter the date as date, and set the format for the cell (or column of cells, or …) to the desired built-in or custom date format. To display a date as March 8, use the custom format mmmm d. It doesn’t matter whether you enter the date as 3/8 or 3/8/08 or 8 Mar or 8-Mar or 8-Mar-08 or March 8, Excel will display March 8.

Reply | Quote

Thanks Hans and all …
I tried the mmmm d and it worked, as did the adding the ‘.
Setting the mmmm d of course saves me adding the ‘.

Thanks again.

Reply | Quote