Wednesday, June 3, 2015

Another cool Excel trick, well two

I stumbled on this one quite by accident.  I like to have my dates completely written out like this:  14 February 1862.  Excel does not allow you to format dates this way, well, it won’t let you if you happen to be in the United States. If you change your location to English (United Kingdom) you can format your dates this way.

DATE 1

 

Another thing I stumbled on.  If you have been using Excel for any length of time you will know that it doesn’t like pre 1901 dates.  You have to format the cells containing dates as Text instead of dates.  I noticed that when I imported a CSV file that I created in Legacy that my pre 1901 dates were intact, they were being treated like text because Excel didn’t recognize them as dates.  A further clue that Excel didn’t recognize them as dates is that they were justified left.  So far so good.  However, the post 1900 dates were imported with two digit years (because of the US setting) and they were justified right (made them easy to spot).  I was able to fix all the dates so that they were uniform and correct.  I want to thank my coworker Sherry from Legacy Family Tree for helping me test this and figure out the solution. 

1) Import the data (CSV file)
2) Change to the English (United Kingdom) date formats and choose DDMMYYYY format*
3) Justify the column to the left (or right) to line all the dates up

* You will need to change to whatever format your imported dates are in.  You will not be able to globally change these to another format because all of the pre 1901 dates are not being seen as dates.  I chose the format that was imported from a Legacy CSV file which is full dates.  If I had my dates entered in Legacy as 15 Jan 1900 then I would have to use that format.

BEFORE

DATE 2

 

AFTER

DATE 3

 

NOTE: You will not be able to sort by the date column.  Excel will only see the post 1900 dates as dates so anything before 1901 will not sort.  If you need to sort by date you will have to split the year from the rest of the date and then sort by the year column, then by the rest of the date column.  These columns will be treated like simple numbers so they will sort properly.

 

Copyright © 2015 Michele Simmons Lewis

4 comments:

  1. Hi, Michele, thanks for the post.

    I just wanted to point out that you can format the post-1900 dates the way that you want to without having to change your location. The trick is using a custom format rather than one of the built-in date formats that Excel provides.

    So in your first screenshot above, rather than selecting Date from the Format Category choices, select Custom. A text box will appear just above the choices in the Type box on the lower right.

    In that newly-appeared text box, type "dd mmmm yyyy" (without the quotation marks), and your date will be formatted with the full month name. Type 3 m's instead of 4 if you want the 3-character version of the month name instead of the full one.

    I should point out that I am doing this in Excel 2000 for Windows (yes, I'm a dinosaur) in the United States, so I can't vouch that this will work for other version in other countries. But it's worth a try in whatever version of Excel you're using wherever you are.

    ReplyDelete
  2. Re: "...dates completely written out like this: 14 February 1862. Excel does not allow you to format dates this way, ..." --- You can also use the Custom category - it's not listed by default, but you can enter "dd mmm yyyy" to create a new custom format entry. (Older versions of Excel don't include the Locale setting)

    ReplyDelete
  3. Well aren't you the smart one! :) I will have to try this. I wonder if you format it this way if it will sort properly? I will have to try it!

    ReplyDelete
  4. Sorry, no. Excel still recognizes the pre-1900 date as text and the custom-formatted post-1900 date as a date, so it is doing a sort on different data types. With a combination of true dates (post-1900) and text dates (pre-1900), if you sort in ascending order, it puts the true dates first and sorts them by date, and puts the text dates last and sorts them like it would any other text (in alphabetical order). Again, Excel 2000. I have Excel 2010 for PC also, but haven't tried it there yet.

    ReplyDelete