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.
Contents
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.
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.
DON’T DO ANYTHING YET. JUST THINK.
AND READ BELOW
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.
- Copy, Paste Special – Formats
- 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!
- 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.
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 —–
9 Responses
We Can use Control H to find the date format and replace with required Data format.
How exactly? That is the question
Find and Replace has an option to select/specify a format (this becomes available when you click on Options), and replace it with the desired format.
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……..
This method is still not fully efficient. Will post the answer soon.
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!
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.
Gangadhar, please check the actual solution. Your solution will work for 1 table. We have multiple tables on multiple sheets.
Find & Replace – Format – Find & Replace – Choose format from Cell – within Workbook. Bingo! 157 changes done. Is this right solution?