Efficiency Test 3: Excel

The Word and PowerPoint efficiency test have become very popular. So here is another one for Excel. It is a very interesting test. The problem looks simpler. But you will learn a lot while finding the solution.

The problem

I have data like this. Lots of dates. Some in dd/mm/yy format and some in dd/mmm/yy format. As long as Excel understands it as a date, technically there is no problem. But practically there is a problem.

If you see a date like 02/06/2014 – how will you interpret it? As 2nd June or 6th Feb?

That interpretation is happening in your mind. Mind has no validations!

If you misinterpret the date, you will take a wrong decision. That is bad.

Here is a sample of the data.

Efficiency Test - Excel date cleanup

Test Excel File (250 kb) Download and Open

Open the file and study it

There are three sheets. There is lots of data scattered all over the place.

Some are numbers, some are dates. There are no formulas.

Some dates are in dd-mm-yy format, some in dd-mmm-yyy format.

Just to make it simpler for you, there is no third variation of date format.

The Challenge

We want all dates to be in dd-mmm-yy format so that the dates show the month in English (or whatever language you have set). This avoid misinterpretation of data.

Think of how you will do it.


Each one of you may think differently. Office is a weird product. You can achieve the same results in many different ways.

The problem is that I am not asking you to use ANY method.
I want the fastest, smartest and the most efficient method.

If you thought about the following methods, you are already inefficient.

  1. Copy, Paste Special – Formats
  2. Format Painter (Even with double click)Why? Because you will end up doing this repeatedly and waste a lot of your valuable time and life!
  3. Did you think of SELECT ALL? That is also a wrong approach.
    Why? Because the numbers will also become dates – in year 1900 or thereabouts!

In short, all methods you can think of are repetitive and consequently time consuming = Inefficient!

Now clear your mind and think afresh. I am sure you will get it with some effort.

This time I am NOT giving the answer in this post.

I will give the answer in the Next Post so that
you continue to worry about finding the solution.

Try it. Here is a hint.

You are trying to find the dates which are dd-mm-yy
and change them to dd-mmm-yy
so that we don’t misinterpret dates.

Don’t think this is a good hint? Think again.



—— Happy Exploration —–

10 thoughts on “Efficiency Test 3: Excel”

  1. Find and Replace format – Find Format (dd-mm-yyyy) and replace format (dd-mmm-yyyy). Now replace all.

    Nitin Sir, u have given gr8 hint. thanks……..

  2. I did a find and replace and used “format”. I only had to do find and replace twice which took care of the 38,000 or so instances. Took less than a minute. Fun!

  3. Ctrl + Shift + Right arrow + Left arrow to select the entire table.
    Right click – Format cells
    Select Date category, Pick the type you want, Select OK.
    You are done.

  4. Find & Replace – Format – Find & Replace – Choose format from Cell – within Workbook. Bingo! 157 changes done. Is this right solution?

Comments? Suggestions? Wish list?