Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining cells - both not text (Excel 97 SR2)

    Is it possible to combine two cells in Excel 97 where both are not formatted for text. Example: I would like to combine 1 cell that has a time reference (1:05) and another cell that has the part of day (AM or PM) so that I get "1:05 PM" in one cell. When I use the command with the "&" sign, it converts the time (1:05) to a text format. Reformatting the new combined cell to "time" does not work. Thank you for your help. Also, if it can be done, would it work the same way in XP? I also have access to that, but not as my primary system.

  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: Combining cells - both not text (Excel 97 SR2)

    I think you can achieve what you want just by formatting the cell with the time value. If you use h:mm AM/PM as the format, 13:05 would display as 1:05 PM. Using 1.05 as a value means 1:05AM.

    Once you concatenate two values, they become a Text value and cannot be used in numeric calculations.

    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: Combining cells - both not text (Excel 97 SR2)

    If the time is in A1, and the AM/PM is in B1, then use this formula:

    <pre>=TIMEVALUE(TEXT(A1,"hh:mm")&" "&B1)
    </pre>


    You will have to format the cell to time format.
    Legare Coleman

  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: Combining cells - both not text (Excel 97 SR2)

    1.05 = 1:12 AM not 1:05 AM

    Steve

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

    Re: Combining cells - both not text (Excel 97 SR2)

    The message said 1:05 not 1.05.
    Legare Coleman

  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: Combining cells - both not text (Excel 97 SR2)

    Andrew stated (he has a decimal NOT a colon)
    <hr>Using 1.05 as a value means 1:05AM. <hr>

    Hence my correction (1:05 as a value would equal 1:05 AM, but 1.05 as a value = 1:12 AM)
    Steve

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

    Re: Combining cells - both not text (Excel 97 SR2)

    Steve

    Thank you for spotting this, but the error on my part was more typographical than mathematical.

    Andrew

Posting Permissions

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