Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Prescott, Arizona, USA
    Posts
    286
    Thanks
    0
    Thanked 1 Time in 1 Post

    date as text (2003)

    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?
    Michael

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date as text (2003)

    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! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Prescott, Arizona, USA
    Posts
    286
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date as text (2003)

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: date as text (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>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

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date as text (2003)

    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??
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: date as text (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>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

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date as text (2003)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date as text (2003)

    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 <code>mmmm d</code>. 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.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Prescott, Arizona, USA
    Posts
    286
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date as text (2003)

    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.
    Michael

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date as text (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>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.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •