Results 1 to 8 of 8
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Formatting dates (2003)

    I have a field of dates entered as follows: "03/01/2006 09:44:39 Wed". They're formmated as General. I must convert them to dates so I can sort correctly. I tried "mm/md/yyyy hh:mm:ss", but that doesn't seem to change anything, perhaps becuase "Wed" is part of the cell. Thanks.
    JimmyW
    Helena, MT

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

    Re: Formatting dates (2003)

    Say these values are in column A, starting in A1.
    In B1, enter the following formula:

    =VALUE(LEFT(A1,LEN(A1)-4))

    Format B1 with a date/time format.
    Fill down as far as needed.

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Formatting dates (2003)

    Thanks very much, Hans. That will work fine. I would have liked to have kept the day name, too, but perhaps that not possible.
    JimmyW
    Helena, MT

  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: Formatting dates (2003)

    You can add it to ther format once it is converted from text to a date:
    Format - cells - custom
    mm/dd/yyyy hh:mm:ss ddd

    Should display the dates as you had before

    Steve

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Formatting dates (2003)

    Perfect! I just added the ddd and pasted the formats. Could I have done that at first, by going to the cell containing 03/01/2006 09:44:39 Wed and applying the format mm/dd/yyyy hh:mm:ss ddd? The cell was formatted as General, so perhaps it first must be converted to the numeric date value?
    JimmyW
    Helena, MT

  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: Formatting dates (2003)

    No, the number formats work on numbers, it would not have affected text entries. Excel can convert the date/time portion (without the weekday) of the text into a true date/time value 9which is what Hans listed).

    The formatting is just to display the number once it is converted.

    Steve

  7. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Formatting dates (2003)

    Thanks again, Steve and Hans. I understand. The text ("Sat") kills the ability to read the cell as a date, so by stripping the text (4, rightmost characters) I can apply a date and then add back the ddd.
    JimmyW
    Helena, MT

  8. #8
    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: Formatting dates (2003)

    That is not exactly it.

    XL stores date/time as numbers. The whole number portion is the number of days from Jan 1, 1900. The decimal portion is the fraction of a day (6AM = 0.25, 6PM = 0.75, etc)

    Your original contents were a "Text date/time". XL did not read it as a real date or time. It was treated as simply text. Adding a number format does nothing to text. By doing Hans' suggestion, it converts the text string into a number. If you format it as "General" you will see what that number is.

    Changing the number format of the cell does not change the number in the cell at all. Its value is still the same. The number format only changes the display of the number in cell. The date/time formats are special in that they can convert the values into all sorts of date/time formats, with or without the day of the week, the year, date, etc. The display is only for that: display. It shows the value differently. The number has not changed.

    Steve

Posting Permissions

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