Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Attached is a spreadsheet with some copied data. For some reason there are some cells that will not format like the others which is messing up a formula I have in another column. In Column A, all cells are formatted as General in order for the formula in Column H to work. Some specific cells (colored purple in the attachment) just refuse to cooperate and I am out of ideas on what to do. All help will be appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It seems to help if you change to a consistent date format.

    The top cells in col A are formatted in mm/dd/yyyy format : e.g. 05/25/2006

    But A5 uses dd/mm/yyyy format : 25/10/2007.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm, I tried that on my end without any luck. The cells with the 'correct' format were actually formatted as 'General' on my end in order to work with the formula. I tried switching them all to the mm/dd/yyyy format but it did not help on the formula end (at least for me) with the problem cells. The other cells seem to be okay either way.

    Thanks.

  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
    For whatever reason the problem cells are formatted as text for excel. To fix this:
    Goto a blank cell
    Edit - COpy
    Select column A
    Edit - paste special - Add [ok]
    Format column A as desired

    Steve

  5. #5
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tried that too. After I do the paste and then format, the problem date cells convert to the numeric value for the date. The 'okay' cells are still fine. I'm honestly baffled. No clue what is going on with these cells.

    Thanks.

  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
    Once you have the numberic numbers you can format as desired. Before this conversion, the cells have text and not the numbers. The values in the cells need to be teh numeric values for the formatting to work

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Could you left justify Column A for the appearance you want?

    Try this formula in Column H
    I used this in cell H5, and then copied it to all other cells in Column H
    =IF((F5/G5)*(2009-YEAR(A5))>F5,F5,(F5/G5)*(2009-YEAR(A5)))

    Tim

  8. #8
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Appreciated! I tried the formula and the left justify and my totals changed. But thank you so much for trying! I think I'm going to have to go with the manual effort with this entire workbook since I can't figure out what is going on with that column.

    Thank you all for the assistance.

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Can you post a worksheet that shows the correct answers that you want in Column H? Use Column J (if convenient) to show what the answers you want are. Then post the worksheet here.

    What should Column H be showing? What is the logic?

    Tim

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    West Virginia
    Posts
    17
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try putting a ' , single quote, in front of the date in the formula bar. There seems to be some type of formatting at the beginning of the contents of the cells in column A that is allowing the other cells to format as General and keep the date as you want. The reason I say something is at the beginning, If I go to say, A1 and at the start of the date hit backspace, it will result in what you have in the purple shaded area.

    After you put in the single quote you can format the as text and then eliminate the single quote if you want. Then you can format as General and it will keep the date as the other cells. Hope this helps.

    Tim M.
    Attached Files Attached Files

Posting Permissions

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