Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Sorting dates in Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sorting dates in Excel

    Has anyone worked out how to make Excel sort a column of dates into an ascending order??

    There seems to be a default (explained in the Help section) to start the list at 1900-01-01. I want it to start at my earliest date which is 1659-03-25.

    When I sort the data by the date column I get all the 1900s up to 1999 first and then it starts on 1659 up to 1899.

    Its driving me nuts :(

    Anne

  • #2
    I have several Excel sheets that are sorted by date and have never had that problem.
    All I do is highlight all the columns on the page, then click Data> sort and choose the column letter that I want sorted. Most of mine have Surname in column A, Christian Name in Column B & Date in Column C, so I sort by column C.
    I am unsure if this is what you wanted, as like I said before, I have never had that problem.

    Comment


    • #3
      Are all your dates stored as dates or are some stored as text, can you check the format of the cells.
      Kathleen

      Comment


      • #4
        I just realized that I don't include the headers when I sort....just in case that makes a difference.

        Comment


        • #5
          I understood that Excel only formally recognises numbers as dates as from 1900. Consequently I have entered my dates in two separate columns - the year in one and the month and day in another, (or if you want to get really pedantic then put the month and day in separate columns as well), then sort the whole table (including the header) by Data / Sort. If you want the months to be in the correct order when you select the sort there is a an Options label at the bottom - click on the drop down arrow and you will be able to make other selections.
          Hope that makes some kind of sense - good luck
          There is no absolute truth - and no final answer.

          Comment


          • #6
            I just type my dates like this...26/06/1685

            Comment


            • #7
              Because I don't know how the next generation of spreadsheets will behave (converting data from Supercalc to Lotus caused problems) I do like Marion, and have three columns. It's a pain, but it works. (And there are Excel functions that enable you to separate out the individual bits in one fell swoop, if you are using modern dates)
              Phoenix - with charred feathers
              Researching Skillings from Norfolk, Sworn from Salisbury and Adams in Malborough, Devon.

              Comment


              • #8
                Sorry - had to go out!

                Well thanks for thinking about it folks - I think I'll just live with it. Like Marion says, I'd already discovered that Excel counts Jan 1st 1900 as 'one' and everything else is arranged round it :(

                I run a one name study and have lists with over 3000 items. Usually I just sort them by 'forename' or 'parish' etc. However, sometimes I want to sort them by date (specially year ) - with the result I described. They are still sorted but just in a weird order!

                Thanks anyway
                Anne

                Comment


                • #9
                  Can you not sort as you currently are - so you get 1900 at the top

                  then scroll down to where it starts at 1695 and hilight the whole of the document from there down

                  then 'cut' it and paste it back in at the top

                  ???? not sure if this will work or it will just resort everything for you again
                  Zoe in London

                  Cio che Dio vuole, io voglio ~ What God wills, I will

                  Comment


                  • #10
                    Well, its a thought, Zoe but my sorting is usually a temporary thing. I tend to sort them out as a 'finding aid'. Usually 'forenames' is the best but sometimes I can spot a pattern better if (for eg) I do it by 'parish' and then 'date'. This results in a list of all the entries for each parish beginning at 1900, going on up the 90s and then starting at the 1600s to 1899!!!

                    It would be a good idea if I wanted to keep a permanent list in date order though - say to print off. Thanks

                    Anne

                    Comment


                    • #11
                      I haven't got Excel on this machine, but if you find an empty column and click on the top cell matching with a row of data.
                      Click function and choose Right
                      Select the cell with the date in it on that row, choose (I think) 4 digits (or it's four digits from digit 7). That should give you just the year. The you can sort by that extra column.
                      Phoenix - with charred feathers
                      Researching Skillings from Norfolk, Sworn from Salisbury and Adams in Malborough, Devon.

                      Comment


                      • #12
                        Hi Anne,

                        I just had a little look out of interest as I didn't realise that Excel did that.

                        When I enter the following on Excel and OpenOffice Calc:

                        Column(1)(2)

                        1999-05-10 F
                        1685-06-26 C
                        1899-10-21 D
                        1900-01-01 E
                        1659-03-25 A
                        1659-03-26 B



                        Sorting on Excel (which you have to PAY for :() - (date column only) produces:

                        1900-01-01 E
                        1999-05-10 F
                        1659-03-25 A
                        1659-03-26 B
                        1685-06-26 C
                        1899-10-21 D

                        as you've already said and which isn't what you want. :(

                        However, sorting on OO Calc (which is FREE) - (date column only) produces:

                        1659-03-25 A
                        1659-03-26 B
                        1685-06-26 C
                        1899-10-21 D
                        1900-01-01 E
                        1999-05-10 F

                        This appears to be what you want. :D

                        I'm sure that there will be a way around the problem on Excel but personally I think it's pretty naff that Excel works that way.

                        Have you ever thought about using OpenOffice? It's pretty easy to use, especially if you are already familiar with Microsoft Office products.

                        Comment


                        • #13
                          If you want to do a temporary sort you could you the Auto Filter function - (Data / Filter / Auto Filter) - it will put drop down arrows at the top of each heading and you can select anything you have entered in a column to sort by. The beauty of this is that you don't have to return everything to as it was by doing another sort - just click on the same arrow and state all and it's all back again. (Sorry - hope I'm not teaching grandmothers (or mothers!) etc!! - I just love playing with Excel!)
                          There is no absolute truth - and no final answer.

                          Comment


                          • #14
                            This is what happens when I sort on Excel.......I can't make it do anything else...weird or what?
                            1672-3-9 E
                            1695-2-3 A
                            1785-2-3 B
                            1892-4-11 D
                            1975-23-1 C

                            Comment


                            • #15
                              It seems to work alright in Mircosoft Spreadsheet sort

                              Comment


                              • #16
                                Anne - It does sort ascending by year if you just use the year and not the day/month. I would find it easier to use Access database to do temporary searches.
                                Kathleen

                                Comment


                                • #17
                                  Thanks for the further suggestions, I will try them out. I'm pleased there isn't an easy answer that I was missing anyway!!

                                  John - I do agree - you would think that Excel (an expensive program on its own) would be able to do better than this!!

                                  Lyn - what version of Excel do you have? Mine is 2003.

                                  Anne

                                  Comment


                                  • #18
                                    Originally posted by Anne in Carlisle View Post
                                    ... my sorting is usually a temporary thing. I tend to sort them out as a 'finding aid'. Usually 'forenames' is the best but sometimes I can spot a pattern better if (for eg) I do it by 'parish' and then 'date' ...
                                    What I've done is sort according to forename, copy this as a new sheet (named Forenames), then sort according to parish, copy this as another sheet (named Parishes), etc... I leave the original sorted by year (all my Excel data is pre-1900).

                                    Rather than re-sorting each time, I can just click on the appropriate sheet, which I find easier.

                                    I don't know if this would help as much if you're adding new entries on a regular basis, but you may want to give it a try.

                                    Tim
                                    "If we're lucky, one day our names and dates will appear in our descendants' family trees."

                                    Comment


                                    • #19
                                      Ah! thanks Tim that's a simple idea I hadn't thought about. I do add entries but they tend to come in batches - inbetween the additions searching is more common. I'll try that one - you can have a lot of sheets can't you.

                                      Anne

                                      Comment


                                      • #20
                                        BEWARE

                                        Last time, I highlighted a column and sorted, only to find that the other columns had NOT been sorted.

                                        I still cannot think of any circumstance where I would sort one column and not the others,

                                        I have a distrust of Excel, I do not consider it user friendly.

                                        If I have more than about 50 records/rows then I prefer a database.

                                        Comment

                                        Working...
                                        X