+ Reply to Thread
Results 1 to 3 of 3

format a cell to use mo/date with no year:,displays 1900?

  1. #1
    SKI CLUB BILL
    Guest

    format a cell to use mo/date with no year:,displays 1900?

    I'M TRYING TO FORMAT A CELL IN EXCEL WITH JUST THE MONTH/DATE 3/14 0R12/1.
    THIS DISPLAYS OK ON THE SHEET BUT WHEN I TRY TO EDIT THE INFO THE YEAR 1900
    IS ALSO DISPLAYED IN THE EDIT AREA?? ALSO WHEN I USE THE INFO IN CONJUNCTION
    WITH THE MERGE WIZARD IN WORD THE 1900 IS DISPLAYED WITH THE DATES. I TRIED
    GOING TO TEXT USING 3/14 BY HIGHLIGHTING TEXT IN UNDER THE NUMBER TAB IN THE
    DATA FORMAT COMMAND BUT THAT JUST REMOVES THE /. ANY SUGGESTIONS?

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    You will have to decide what to do but the reason you get this is simple but not often understood.
    Excel uses a number for date and time the part to the left of the decimal point is the number of days from the 1st Jan 1900; and the part to the right of the decimal point is a fraction of 35 hours which gives a time. Formatting a cell to display only a day and month (or just a year, or hust a time etc) does not mean you have somehow got rid of the rest of the date and time.

    you can choose to use a text field but then you will have other problems particularly if you want to sort a column of dates.

    regards

  3. #3
    dodong
    Guest

    Re: FORMAT A CELL TO USE MO/DATE WITH NO YEAR:,DISPLAYS 1900?

    taking your example above 3/14/1900 try this
    =Text(A1,"mmmm,dd")
    this will give you March, 14
    =Text (A1,"mm,dd")
    this will give you 03,14

    I hope this will help.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1