In the NICAR-L forum, Jessica Glazer added a post on January
14 regarding formatting dates before 1900 in Excel.
Glazer wanted to find the best way to uniformly format the
dates she had in her data set.
There were a few suggestions that were given and further
explained throughout this thread.
The most common excel tip that was given to Glazer was to
use a text field and the yyyy-mm-dd format. This format is said to have the
benefit of sorting correctly as text and it ends up being chronological.
I learned that because it’s delimited, it would be easy to
do date arithmetic. The downfall from doing this is the loss of automatic
validation of a date field. This means that the one who is looking through the
data set must find mistakes such as “1799-02-31” on their own.
In this thread, Thomas Wilburn said this format is harder to
filter.
To solve that problem, other columns could be used for
filtering. Multiple columns can be sorted or filtered on a single column with
predictable delimiters.
A delimiter is a character or a series of characters that
indicate the beginning or end of a statement or body set. Examples of
delimiters are round brackets: ( )
Here is the formula that Wilburn left for sorting multiple
columns in Excel:
"=CONCATENATE(A1, "-", TEXT(B1, "00"), "-", TEXT(C1,"00"))"
"=CONCATENATE(A1, "-", TEXT(B1, "00"), "-", TEXT(C1,"00"))"
I learned that separate columns could be sorted and
filtered. With single text columns sorting is no problem, but filtering isn’t
fun.
Even though filtering isn’t the most “fun” of all things, it
should always be done and will be useful.
Wilburn said, “I feel like I’ve rarely regretted breaking
something out into separate fields, but I have regretted not doing it.”
Edward Borasky added an insightful tip about Excel’s date
algorithm. An Excel spreadsheet will display data for February 29, 1900. This
is because Excel thinks 1900 was a leap year and will continuously think this
for 1800 and 1700.
After reading through this thread, I learned that data
journalists are willing to help and answer a variety of questions. They could
reply within a couple days, a few hours, or seconds after the question was
posted.
In this thread that Glazer posted, Tim Henderson and Thomas
Wilburn were having a mini-conversation between themselves. They were
commenting on each other’s comments and questioned each other’s suggestions.
With that said, Glazer was not the only one who learned tips on excel formatting, so did Henderson, Wilburn and everyone else who
posted in the thread.
The data journalism community is a group of very knowledgeable
people who are experts in their own specific fields and are more than willing
to share that knowledge to help others from around the world.
I love how you wrote the summary just as if were teaching someone the tricks that you had learnt. One thing missing that may have helped your summary was a URL to the thread or the title so I could look it up myself, I may have missed it somewhere. Overall your summary embedded the "Speak to the Geek" concept because I too agree that most journalists are willing to share their knowledge to help others.
ReplyDeleteGreat summary on the thread that you read. I definitely understood your topic and what you learned about. I'm sure that it doesn't happen to journalists often to use dates before 1900 but it is good to know that their is a way to do it if I ever come across that. My only concern was that I did not know what a delimiter was and I feel that you explained that a few paragraphs after what I needed to know it for. Although this seemed like a complicated topic so I can understand summarizing it is not easy. I agree that the data journalism community is very knowledgeable and I am very happy that we have a way to communicate so we can all help each other.
ReplyDeleteI have never come across this myself, but now that you mentioned it I assume that sorting through data prior to the 1900's would be a pain in the ass. It would be a tedious process to filter through all the results, although as you mentioned an important thing to do. You can tell you have some experts to hear back from in NICAR-L, since the two "geeks" giving answers decided to question each others answers on the thread.
ReplyDeleteIt's crazy to think that these people are willing to share even the tiniest little tips that they've figured out themselves, like the fact that Excel thinks 1900 was a leap year. Very interesting post, I like that you explained everything clearly.
ReplyDeleteIt's good to know that, even though it seems tedious, there is a way to format dates before 1900. I guess even though it may take a while to sort through all the data at least it's possible that you can sort through the data some way, rather than having no way.
ReplyDeleteThe post you looked at seems to be quite a complicated one. I like that you explained everything that you learned from it and it's good to know that people on NICAR-L are willing to take the time to help others.
ReplyDeleteI find it really helpful that you quoted the thread that you used for your assignment because it really helped me understand the post better. I think it's kind of funny that Excel thinks that 1900 was a leap year but it's something I never would have thought to consider so this was a really useful thread even to a data newbie like myself.
ReplyDelete