Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    green triangle in date cell (office xp pro 2000)

    i have a file of three columns in which the end column is date's, i am attempting to sort the file in date order but it gives an error due to some of the date cell's having a green triangle in the left hand corner.

    if i select the cells with the green triangle, the error shows as.
    text dates with two digit year
    convert xx to 20xx
    and the column seems to behave.
    dose any one know how to change the date so that i can include it in a macro normal formatting of the cells dose not seem to work

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

    Re: green triangle in date cell (office xp pro 2000)

    Could you upload a workbook that shows the problem. You can delete everything except the column with the dates. This could be several things, and it will save time narrowing it down. I also havent seen the green triangle before, so I am curious what it is.
    Legare Coleman

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: green triangle in date cell (office xp pro 2000)

    The green triangle is a feature of Excel 2002 and Excel 2003 (not Excel 2000)
    It indicates that the cell contains data which is in a slightly different 'format' to those around it.
    It is used to help show discrepancies.
    For example, if you have a column of numeric entries and one of them is entered as text (e.g. by preceding with an apostrophe) it will show with a green triangle in the upper-left corner of the offending cell.
    When you put the cellpointer on the green triangle a drop-down will show the type of discrepancy.
    There are other 'new' features as well.
    I remember there is a facility for turning this green triangle lark off if the data was in fact how you wanted it.

    zeddy

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

    Re: green triangle in date cell (office xp pro 2000)

    Thanks for the explaination. I guess I will never see that feature since I refuse to install any system that includes the Registration/Activation wizard. The version in the post's title showed XP Pro and 2000. That must have been an error.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: green triangle in date cell (office xp pro 2000)

    thank you for your quick responses ,i will post a copy of the file tommorow

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: green triangle in date cell (office xp pro 2000)

    the initial down load is a csv file and by changing the parameters for the date column from text to " date " (silly i know ) it now works the problem which persists is that column A dose not sort as you would expect.
    what i have or hope to have is the same data but shown in two different ways, DATE order which i have overcome and COMPONENT order which is still a pain.
    i have included a copy of the excel file

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: green triangle in date cell (office xp pro 2000)

    sound very easy, will try at work tommorow, in 2 you say
    2- Enter this formula into cell B2 (the new column).
    i might off missed something here but what formula do yo mean?

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

    Re: green triangle in date cell (office xp pro 2000)

    The reason you are having problems sorting on Component is that some of the values in this column are entered as text and some of them are entered as numbers. You need to convert all of them to text to get it to work properly. Here is a way to do this:

    1- Select Options from the tools menu and on the Calculation tab set calculations to Automatic.

    1a- Insert an empty column between columns A and B.

    2- Enter this formula into cell B2 (the new column).

    <pre>=IF(ISTEXT(A2),A2,TEXT(A2,"0"))
    </pre>


    3- Copy the formula down as far as the Component ID go in column A (hint: Double click on the cell B2 fill handle).

    4- Select all of column B and then Select Cells from the Format menu and format the column as text.

    5- Select Copy from the Edit menu.

    6- Select "Paste Special" from the Edit menu.

    7- In the resulting dialog box, put a tick mark next to "Values" in the Paste section.

    8- Click OK.

    You should now have a column B with all of the Component IDs converted to text. Copy and paste the column header from column A to B and then delete column A. You should now be able to sort on component and have it behave as you would expect it to.
    Legare Coleman

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

    Re: green triangle in date cell (office xp pro 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> OOPs. I have edited my previous post to insert the formula. I also added a new step 1 which I forgot about.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: green triangle in date cell (office xp pro 2000)

    with certain modifications to your text it now works a treat
    1- Select Options from the tools menu and on the Calculation tab set calculations to Automatic.

    1a- Insert an empty column between columns A and B.

    2- Enter this formula into cell B2 (the new column).



    =IF(ISTEXT(A2),A2,TEXT(A2,"0"))

    3- Copy the formula down as far as the Component ID go in column A (hint: Double click on the cell B2 fill handle).


    3A- f9 to update (calculate) (i prefer to have my settings at manual)
    4- select all of column B (which is still selected from instructrion 3) and format as text
    5- delete column A
    finito works a treat
    thank you very much for all your help

Posting Permissions

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