techo help please - spredsheets

[jillybean]

Hi, I am working with our Units membership list, compliments of the ACBL downloadable file.

 

I have imported the list into an open office spreadsheet and all is fine except that I cannot

get the spreadsheet to sort in any sensible order using the 'last activity date' column.

The date is in the format mm/dd/yyyy so it sorts by month when I want year/month.

 

I can't seem to change the format of the date using the spreadsheet controls, I tried to change the

format of the date field when opening the file but this had no effect.

 

Can anyone help?

 

I know the 'last activity date' field is not entirely useful as it records last activity logged

which could be a change of address, payment rather than MP earned but I'm hoping it will be

a starting point to see which U members have stopped playing :)

[jjbrr]

im sure someone will think of something more elegant but I'm pretty simple, so I'd just change the format to yyyy/mm/dd

[blackshoe]

Hm. I opened a new spreadsheet in NeoOffice (a Mac implementation of Open Office) and entered four dates in a column in mm/dd/yyyy format, and sorted them. It did so correctly by year, then month, then day. I don't know why Open Office itself would be any different. Sorry, I guess I can't help.

 

In NeoOffice, changing the format is pretty simple. Again, don't know about Open Office itself.

[EricK]

If the spreadsheet doesn't change the format then I suspect it is because is doesn't recognize those fields as dates, but is just treating them as strings. I don't know Open Office well, but you should be able to turn them into dates via a formula. In Excel i would use the MID function to extract the left 2 characters, the 4th & 5th characters, and the right 4 characters and then use the DATE function to construct a date from them. I expect Open Office has the same functionality even if the function names are not exactly the same. Once you have done that, you should be able to format the dates as you please and sort them how you like.

[TimG]

See if you can use month() and year() to extract the month and year info from the dates. Then sort by those.

[wyman]

I just tried this in openoffice and it worked fine. Is the cell a 'text' cell? If the values are just strings, I'm sure you can parse the string around the '/' so that you get new columns with mm, dd, and yyyy, and then you can recombine them into the desired format in yet another column.

 

Does this make sense, or is it mumbo-jumbo?

 

edit: errr -- beaten to the punch by #3 and #4 :)

[jillybean]

What you are telling me to do makes sense, HOW to do it may prove more difficult.

[EricK]

What you are telling me to do makes sense, HOW to do it may prove more difficult.

Assume the data is in column A, starting at cell 1. Insert a column to the right of that, and then put this formula in cell B1.

=DATE(MID(A1,7,4),MID(A1,1,2),MID(A1,4,2))

Then copy that formula down the entire column.

 

At least that is how I would do it in Excel if the problem is as I suspect.

 

Another possibility is to try to force it to convert to a date. Again insert an extra column and simply type the formula:

=A1+0 [Where A1 is the the relevant cell reference].

 

Then see if that column can be formatted as a date.

 

As this is simpler, I would try this latter approach first.

[jillybean]

Do I need to put the forumla in any bracket or parenthesis?

I get a #VALUE returned when I simply type =S1+0

 

I'm going out, will check on this later thanks!

[EricK]

Do I need to put the forumla in any bracket or parenthesis?

I get a #VALUE returned when I simply type =S1+0

 

I'm going out, will check on this later thanks!

If you get a #VALUE then OpenOffice is definitiely not interpreting the cell as a date (and perhaps doesn't like performing implicit conversions!).

 

Another possibility which springs to mind is that there are some leading or trailing spaces in the string. If in your extra column you try =len(S1) you will see how many characters are actually in the string. If it is more than 10 then you have some leading/trailing spaces. If so, try =TRIM(S1)+0 to see if it will implictily convert that. And if that doesn't work, use the longer formula but replace each reference to "S1" with "TRIM(S1)"

[jillybean]

Length = 10

[wyman]

One way is Data-->Text To Columns (you'll then have to click "other" and type '/' (no quotes)). This will turn A1 into A1/A2/A3, separate fields for M-D-Y.

 

Another (if all of your data is mm/dd/yyyy, e.g., 01/01/1993 not 1/1/1993) is to grab the left 2, right 4, and middle 2 starting in position 4 and convert to date:

 

DATE( RIGHT(A1, 4), LEFT(A1, 2), MID(A1, 4, 2) )

 

[The DATE function takes (Y,M,D) as args]

 

edit: bah, this was in #8. I'm 0/2 :(

[nigel_k]

It definitely sounds like it is a text value, not a date value, and is being ordered accordingly.

 

The simplest way is probably to create a new column with just the year in it. Let's say your data is in E2. Insert a column on the right (which will be F2) and put in the formula: RIGHT(e2,4), or maybe SUBSTRING(e2,7,4). Then copy that formula to all the other rows and sort on column F then column E.

[jillybean]

Eureka!

 

Thanks Nigel, and all.