Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Time (XP)

    Hey there

    I'm trying to format numbers that have been set to actual times (i.e. 9:00 am) to text. In other words, I want it to read 9:00 am - but not as a number. Is there a quick way of making this happen? I've tried copy and paste special (as values) and have though about going through several hundred rows of data and adding the (') symbol in front. If there is a faster way (that won't reformat my numbers) then I would be greatly appreciative!

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting Time (XP)

    Times are stored as numbers, and regardless of the formatting they retain their numeric value. However you can convert a timevalue to text with a formula as follows :

    =TEXT(A1,"h:mm AM/PM")

    This will convert the numeric value in A1 to a text string representing the time value in AM/PM format.

    If required you can copy the results of the formula and paste special as values. This would allow you delete the original values.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Time (XP)

    You should change the format from General to Text between inserting the formula and the Copy/Paste Special Values.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting Time (XP)

    Legare,

    This step (changing format) does not appear necessary in Excel 2002.

    The TEXT function returns text , and text values live happily in a cell formatted as General.

    Andrew.

  5. #5
    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 Time (XP)

    I second Andrew's comment for XL97, the format change is not needed.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Time (XP)

    But if you type in a new time, it will become a time value not a text value.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Time (XP)

    I think it is. See my reply to Andrew.
    Legare Coleman

  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 Time (XP)

    I see where you are going ...

    Andrew and I were not thinking far enough into the future. You don't need to format to convert the values. You suggested the format change to prevent future entries from needing to be converted at all. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    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
  •