I always forget how to convert simple text strings into dates in Excel

Despite having the world’s knowledge at my finger tips (i.e. the internet), I always forget little tips on how to do things in Excel. Yes, there’s bookmarks and other different ways to save websites, but I can never find them again after I saved them! So maybe writing a blog about it might be a good way to remember and also to help other people out there who come across this same problem.

This is one of the most annoying things when working with text and numbers in Excel… getting text to be in a “date” format. It makes life a whole lot easier when running queries or trying to sort/filter/order numerical values rather than text. While there are a lot of ways to do it (see this link for example), the following method is the most straight-forward way for me to convert text into a date/number and it works every time.

So here goes…

You come across dates as a string of text and they look like this:

  • 1.1.2015
  • 1.2015
  • 01 01 2015
  • 2015/1/1

In my example, the dates come out as dd/mm/yyyy format but it really is text.

Screen Shot 2017-03-25 at 2.34.20 PM

Select the text you want to convert and then go to ‘Data>Text to Columns’

Screen Shot 2017-03-25 at 2.36.22 PM

A dialog box will open. Just keep the selection as ‘Delimited’ and click ‘Next.

Screen Shot 2017-03-25 at 2.36.58 PM

On the next dialog box, select which ever delimiter is what separates the day, month and year for your text dates. In my example, “/” separates my text so I selected ‘Other’ and then inputted ‘/’.  This puts each number into its own column. Click ‘Next’.

Screen Shot 2017-03-25 at 2.38.55 PM

In the final dialog box, just keep everything the way it is and change the destination to where you want the new columns to be added. Click ‘Finish’.

Screen Shot 2017-03-25 at 2.41.31 PM

This puts each number from the text dates into their own column.

Screen Shot 2017-03-25 at 2.44.03 PM

Now I need to combine the numbers into the format that I want (yyyy-mm-dd). This requires a simple equation to merge the values in each column based on the order of the values I want.

=(D2&”-“&C2&”-“&B2)

Screen Shot 2017-03-25 at 3.46.03 PM

Once the numbers are merged, I need to copy the values (not the equations) into a new column in the “date” format. Under ‘Edit’, select ‘Paste Special’ and then choose ‘Values’. Click ‘Ok’.

Screen Shot 2017-03-25 at 4.03.24 PM

While the converted value is a number, in order to turn it into dates that Excel can recognize, I need to run a simple equation using ‘=Value (cell number)’.

Screen Shot 2017-03-25 at 4.04.35 PM

I then redo the ‘Paste Special’ process on the ‘Value Equation’ column to move the values into a column that is formatted as ‘Date’.

Screen Shot 2017-03-25 at 4.07.01 PM

There’s probably a quicker way to do this, but this is the best way I’ve found that provides the most control on addressing the number rather than fiddling with Excel’s functions to format columns… and it works every time.

OCHA hosts UN Designers Group in Geneva

A few months back, I wrote about how Geneva is a melting pot of communication professionals with plenty of active groups meeting to exchange ideas, challenges, and solutions to a constantly evolving field of work. I’m proud that ITU brought together the UN (graphic) designers group late last year with an inaugural meeting in November. Among the UN participants, it seemed that OCHA was ahead of the curve in integrating design into their work and so we were asked to host the second meeting. In May, we were finally able to get our butts in gear to host the meeting. While the first meeting was informal and over lunch, this time around lunch was still on the table, but we also wanted to put a little bit more structure and decided to focus on a key element of how design is used in OCHA’s work, particularly in times of emergencies. This usually means turning lots and lots of data and information from natural or complex disasters into easily accessible and understandable formats that can be used for advocacy, awareness-raising, planning and decision-making.

UNdesignerslunch_May20

We explained how design is integrated into the humanitarian response process and how more and more visual design and communication is becoming an area in which other technicians (i.e. information managers and communication generalists) need to understand and be involved in despite the lack of knowledge and know-how. The basic idea is that there’s now so much information that comes through in times of disasters and emergency, it’s hard to make sense of it all and for it to tell a story that can help people understand the severity of the situation or to use this information to make the best decisions possible.

challenges

Not only is timing, data-reliability and access a challenge, but so is the issue of standardization and consistency. This means being able to do quick designs based on templates and standards because different people get involve in the design process at difference times during an emergency. But this also has an impact on the branding and consistent communication by the organization. One of the main challenges is to ensure that products meet a certain standard to ensure that an organization’s ‘brand’ is upheld and that people can trust the source of information because, in addition to the quality of the content, the visual identity and look/feel is also what provides value to the reader/user.

Thanks to the OCHA team for a great presentation, and to all the ones who attended the second lunch / meeting!

Posted by UN Geneva Graphic Design Community on Friday, May 22, 2015

There’s also always the constant challenge of “making things pretty”, which is what most people think of designers in this field, vs. “making things useful” – why would you take a 20-page document and turn it into something “pretty” only to have people read it… does that mean if it wasn’t pretty then people wouldn’t read it – what does that say about the document? An interesting discussion point around this was the fact that designers are always asked to help on ‘formatting’ which is actually different for ‘designing’ something. If you’re interested on the influence design (not formatting) has on our lives, “The Design of Everyday Things” by Donald Norman is a must read…

There is no need to sacrifice beauty for usability or, for that matter, usability for beauty.

The most interesting part of the meeting was still over lunch where we talked about how people got into design (a lot of people didn’t come strictly from a design education), the challenges/stigma designers face in and outside of the office, and how visual communication is now part of ‘mainstream’ communication and what that means for designers. Like the Facebook page to get info on the next meeting and to stay in touch with the group and get news about design stuff.

UNdesignersgroup

Data is good. Data is bad. Where’s the middle ground?

It’s amazing to see the use of technology to track, monitor, and collect information and data from things that we do, like sports, to help improve and enhance what we do. I think it’s called life-hacking and if you’re thinking of resolutions for the New Year, there’s a whole website about doing just that. Yet as we get more and more comfortable tracking and hacking ourselves (FitBit anyone?), I can’t help but think – are we getting too reliant on the data?

I’ve written a couple posts about how data has been used to better predict and inform baseball and basketball. Yet a prime example of the over-reliance on data, and not balancing it with common sense, comes from the sports world. In this article, it shows that despite all the hype around big data for the 2014 World Cup, Nate Silver, a popular data geek who made predictions during the 2012 US presidential election, still got it all wrong. Some said that the competition was no place for big data, which can’t understand the intrinsic issues and subtleties that real soccer fans see. Others claimed that Silver ignored some basic data issues.

IMG_0333

Data and information that we collect whether through technology or by ourselves inherently has biases, like how someone setup these exit “sortie” signs for a reason. We build the technology and develop algorithms that are supposedly objective yet in developing them we make inherent compromises and assumptions. The same goes with collecting and compiling data ourselves – from monitoring our diet, building a contact/email list, or just keeping track of our to-do lists and calendar – we are biased to certain things (ex. what we think is more important, what we can remember, etc.) when collecting this information (i.e. Excel sheets anyone?). Also are we managing our information consistently enough so that it can reveal some truth that can help our decision-making?

P1150774

I work for an organization that prides itself on its “information management” and spend a lot of time with internal and external clients to not only improve this management (ex. simplify, organize and clean the data), but also to understand how it can be used strategically to communicate their work and key messages (ex. like making a good infographic). Within the international development community, OCHA is light-years ahead of the game when it comes to this. They’ve also evolved and branched out to apply information in a useful way for the humanitarian community like the recently launched INFORM initiative to improve risk analysis and the Humanitarian ID project to make contact management simpler and better for an emergency or crisis. Perhaps following OCHA’s lead, there are plenty of UN organizations starting to visualize this information and realize that data is more than just 1’s and 0’s or that it’s only for “geeks”, but can be used in different ways to communicate and provide “evidence” to improve programming and decision-making. The success of innovative ideas like these will depend on how accessible both the data and tools are to the people who will use them. It can also be summed up by these two quotes from the Nate Silver article:

Predictions are no better than the quality of data and model that you employ.

Big data and predictive techniques are supposed to inform smart decision making, not automate it.

On the opposite end of the spectrum of the Silver article are these little visual vignettes by the New York Times of what went right for the dutch, and so wrong for Brazil during the world cup. They are both data-driven and informative.

Data simplicity might be the best way to help us understand and improve the way we do things.

Filtering out “Butt Dials” and saving lives with technology

P1140023

I find the combination of Qatar and humanitarian action an unlikely partnership. It’s just not something that comes to mind when I think of how the world has come together to better respond to and address large scale disasters like the 2010 Haiti earthquake, the 2011 Great East Japan earthquake and tsunami, or even the current situation of Typhoon Haiyan that just passed through the Philippines and on its way towards Viet Nam and upwards to Hong Kong and China. Yet, the Qatar Computing Research Institute (QCRI), a national research institute established in 2010, has partnered with some of the most innovative thinkers and doers to come up with ways to understand how better to respond and understand disasters, and ultimately save lives.

One of these thinkers is Patrick Meier and doers is the UN Office for the Coordination of Humanitarian Affairs (UNOCHA). I was fortunate enough to have been invited to a talk earlier this week where Meier came by to chat about the role of data (i.e. big, global, social, etc.) and technology in humanitarian action, a young field filled with promise. Meier is an internationally recognized thought leader on the application of new technologies for crisis early warning, humanitarian response and resilience, serves as Director of Social Innovation at the QCRI, and is an avid blogger. A shout out also goes to Andrej Verity of UNOCHA who organized the talk and is passionate about the role that (digital) volunteers and technology can play in humanitarian response.

During the one-hour talk, Meier went through a range of topics from human vs. machine computing, microtasking, the power of digital volunteers, dealing with the overload and verification of information, data philanthropy, credibility of communication, and how to find a balance or the competitive advantage between new and traditional sources of information. All the focus was on the humanitarian space and how to better respond to and provide relief in times of disasters or emergencies. Yet, the concepts behind the talk – how data can be used to make better decisions, improve planning, and create a more resilient and prepared society – can be (and is already being) applied to areas beyond the humanitarian space, for instance in social and environmental development. They key is how to take advantage of technology, the benefits of having human judgement, and how to make sense of all this “stuff”.

Overflow of information is as paralyzing as with no information.

The focus shouldn’t just be on tech because we still need “detectives” to verify information, even if algorithms and computers get better at fact checking. At the same time we also need to understand that traditional ‘crowdsourcing’ methods and ‘trusted sources’ of information also aren’t as reliable as we think they are. Here are a few notables Meier brought up during his talk:

  • BBC has been verifying user-generated content for years
  • Storyful uses skills from journalism to verify info/data by helping journalists, broadcasters and publishers filter breaking news, trending stories and local sources from the noise of social media.
  • NY Times has 7000 factual errors a year – while I couldn’t find evidence to back this up, this article highlights that “A landmark study in 2005 found that more than 60 percent of the articles in a group of 14 newspapers contained some kind of error.”
  • 911 is a crowdsourcing system collecting info from the “crowd”, yet millions of false calls are made to 911 every year, or as Time Magazine says, “Almost 40% of New Yorks 911 Calls Are Butt Dials

hina

Another reason why people are an important factor in all this talk about technology and the use of information for decision-making is the fact its use will be defined by society and governments. There needs to be a political will and space needed to move (humanitarian) technology and information/data forward and to be taken seriously. Released a couple of months ago, the UNOCHA report “Humanitarianism in the Network Age (HINA)” sums it up nicely on page 41:

Technology can only be as effective as the system it supports. What is clear is the need for Governments and the international humanitarian system to open themselves to new approaches.