No Alignment! I can\u2019t stress how important not to do this!<\/strong><\/p>\n\n\n\n Remember I\u2019ve 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\u2019s 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\u2019re hiding the fact that which is the date that Excel recognizes and which it not.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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?<\/p>\n\n\n\n Some may say May, some say Jan. It is bad when you started to hesitate the data you\u2019re looking at. The person who entered the date can mean it as May or Jan. Alright, here\u2019s 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\u2019re dealing with the date and time in Excel, you\u2019re aware that the date should be in d\/m\/y or m\/d\/y.<\/p>\n\n\n\n One you\u2019re in the setting of date and time, you should be able to find the setting call \u201cChange Calendar Settings\u201d. It will follow by this dialog as shown below.<\/p>\n\n\n\n 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\u2019ve 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.<\/p>\n\n\n\n If you\u2019re 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<\/strong> agreeing anybody to do that at all, don\u2019t even think about doing it!<\/p>\n\n\n\n Let\u2019s 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 \u201c1\/1\/2017\u201d and then change the format of date using custom \u201cd.m.yyyy\u201d.<\/p>\n\n\n\n After the format is applied.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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\u2019ve just entered, enter this \u201c1.1.2015\u201d then check out the alignment.<\/p>\n\n\n\n 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\u2019re in crazy trouble.<\/p>\n\n\n\n My best advice for you, don\u2019t do anything to the date at all. Besides making sure while you\u2019re entering the date, you follow strictly like how it is being set in the Windows.<\/p>\n\n\n\n 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\u2019m using is by m\/d\/yyyy. Hence my Excel will only recognize if the date is being enter as such format \u201cm\/d\/yyyy\u201d.<\/p>\n\n\n\n The date column shown below has been applied with right alignment. Before starting, remove the alignment. In the previous solution I\u2019ve talked about how to remove alignment, please refer to the last solution.<\/p>\n\n\n\n After the alignment is remove, now you will be able to find out which date is the one being treated as string.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Select next twice until you get to this view as below.<\/p>\n\n\n\n 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\u2019re going to do is tell Excel what format of the date that are being listed in the highlight.<\/p>\n\n\n\n In this situation, format of DMY will be selected. Once done, select finish.<\/p>\n\n\n\n Now check out the date. Excel automatically correct the date into the format as how the Windows is being set.<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" Microsoft Excel Best Practices Don\u2019t! Do not change the formatting of date Okay, let\u2019s talk about date. This \u201cDon\u2019t\u201d is related to the \u201cDon\u2019t\u201d from the list above. Let\u2019s do this. Insert these 2 dates into separate cells, make them side by side. The first date \u201c1\/31\/2015\u201d, the second date \u201c31\/1\/2015\u201d. *widen the width of these […]<\/p>\n","protected":false},"author":4,"featured_media":605,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1051","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=1051"}],"version-history":[{"count":6,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1051\/revisions"}],"predecessor-version":[{"id":2208,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1051\/revisions\/2208"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/605"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=1051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=1051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=1051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"https:\/\/www.info-trek.com\/blog\/wp-content\/uploads\/2024\/02\/image-46-1024x414.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.info-trek.com\/blog\/wp-content\/uploads\/2024\/02\/image-47-1024x419.png\")
<\/figure>\n\n\n\nSolution for date problem<\/h2>\n\n\n\n