green triangle in date cell (office xp pro 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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).

=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).

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.

Reply | Quote

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?

Reply | Quote

OOPs. I have edited my previous post to insert the formula. I also added a new step 1 which I forgot about.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

OOPs. I have edited my previous post to insert the formula. I also added a new step 1 which I forgot about.

Reply | Quote

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?

Reply | Quote

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).

=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).

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote