Microsoft Excel Best Practices

Don’t! Do not change the formatting of date

Okay, let’s talk about date. This “Don’t” is related to the “Don’t” from the list above. Let’s do this. Insert these 2 dates into separate cells, make them side by side. The first date “1/31/2015”, the second date “31/1/2015”. *widen the width of these 2 columns*.

No Alignment! I can’t stress how important not to do this!

Remember I’ve mentioned in the earlier point, Excel will align string and labels to left while values to the right. Before we deep dive into why the alignment in these 2 dates are different, let’s talk about how Excel analyze data. Excel stores date using numbers. The number 1 represents 1 January 1900; 2 represent 2 January 1900; 42736 represent 1 January 2017. Hence, date is considerate as value because that is how Excel stores date. When formatting like alignment took place, we forcefully align them to left and right, we’re hiding the fact that which is the date that Excel recognizes and which it not.

Right alignment has applied to the both dates, now we can hardly differentiate which is label which is value. The worst part is when these 2 dates is hiding within thousands of rows in a column and we can hardly differentiate until the alignment is removed. Then you can spot which is the string or value.

Back to the topic, when is the date formatted as d/m/y or m/d/y. I guess these 2 formats is the most confusing format. When I insert this date into the cell and I ask my student this question, this is May or Jan?

Some may say May, some say Jan. It is bad when you started to hesitate the data you’re looking at. The person who entered the date can mean it as May or Jan. Alright, here’s the second lesson about date you need to know, which is date formats. The date regional setting of windows affect how Excel interprets dates. You can find the setting in the Control Panel section, Date Time region. It is important for you to know what date format and settings in your Windows, so while you’re dealing with the date and time in Excel, you’re aware that the date should be in d/m/y or m/d/y.

One you’re in the setting of date and time, you should be able to find the setting call “Change Calendar Settings”. It will follow by this dialog as shown below.

This is where you modify the date format setting. Once the format here is set, how Excel analyze date will immediately take effect. It is possible to have different user with different date format setting in their computer, all you must do is follow strictly by how the date format that you’ve set. Once the file is being transfer to a computer with different date format being set in the computer, the date in Excel will immediately adapt to current formats. So, no worries if you and your team member having different date formatting being fixed in the system.

If you’re aware, Excel give user to own all the power to change how the way a date looks in the Date Formatting section. User has full right to customize the date into how the way they like it in the custom section, which 100% me NOT agreeing anybody to do that at all, don’t even think about doing it!

Let’s continue reading, then you will realize this is the biggest sin that you can ever do to date in Excel. Enter this into a cell “1/1/2017” and then change the format of date using custom “d.m.yyyy”.

After the format is applied.

So what you just did it you transform the appearance of the date from d/m/yyyy to d.m.yyyy. Place your cursor on the date that you just formatted and look at the formula bar, all you did is changing the outlook of the date, the actual value of the date is still d/m/yyyy.

Doing this you are making other teammate whom handle this worksheet thinking date can be written that way. And now, beside the date that you’ve just entered, enter this “1.1.2015” then check out the alignment.

So apparently Excel do not understand when a date is being entered d.m.yyyy. Again, if some date is manually entered that way, and some date are the change has been formatted to look that way, within many thousand rows od data, you’re in crazy trouble.

My best advice for you, don’t do anything to the date at all. Besides making sure while you’re entering the date, you follow strictly like how it is being set in the Windows.

Solution for date problem

This solution is to find out those date that are unrecognizable by Excel. Please note that with different date setting in your Windows, the way Excel recognizing the can be slightly different. The Date setting in the computer I’m using is by m/d/yyyy. Hence my Excel will only recognize if the date is being enter as such format “m/d/yyyy”.

The date column shown below has been applied with right alignment. Before starting, remove the alignment. In the previous solution I’ve talked about how to remove alignment, please refer to the last solution.

After the alignment is remove, now you will be able to find out which date is the one being treated as string.

Now begin to sort the column, Ascending. This step is to group all the string and date. Now select the range of date that is being treated as string then click Text to Column command.

Select next twice until you get to this view as below.

GO to the date section and select the date format that matched the format shows in the data preview. In this example, Excel do not understand the date within the selection. So what we’re going to do is tell Excel what format of the date that are being listed in the highlight.

In this situation, format of DMY will be selected. Once done, select finish.

Now check out the date. Excel automatically correct the date into the format as how the Windows is being set.

Miss the Part 1 of this article? Click here to read on ‘Deadly sins you should not do in Excel‘