How to change your system's default date and time formats in Excel
Have you ever wanted to have your date in Excel stored in one format (for instance dd/mm/yyyy) and the system keeps reading it the way it deems fit (for instance, mm/dd/yyyy) and/or you are forced to follow the system's default settings? Here is a good learning that will give you the power to define the kind of life you wish. Enjoy & feel the liberation!
Before you can take advantage of powerful Excel date features, you have to understand how Microsoft Excel stores dates and times, because this is the main source of confusion. While you would expect Excel to remember the day, month and the year for a date, that's not how it works...
Excel stores dates as sequential numbers and it is only a cell's formatting that causes a number to be displayed as a date, time, or date and time.
Default date format in Excel
When you work with dates in Excel, the short and long date formats are retrieved from your Windows Regional settings. These default formats are marked with an asterisk (*) in the Format Cell dialog window:
How to change the default date and time formats in Excel
If you want to set a different default date and/or time formats on your computer, for example change the USA date format to the UK style, go to Control panel and click Region and Language.If in your Control panel opens in Category view, then click Clock, Language, and Region > Region and Language > Change the date, time, or number format.
On the Formats tab, choose the region under Format, and then set the date and time formatting by clicking on an arrow next to the format you want to change and selecting the desired one from the drop-down list:
Learn a lot more about Formatting Date and Time in Excel here: https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/
Source: Ablebit.com
as your article was about as lame as it could possibly be here is the solution - a hack I found online. Hope this helps any other poor souls who have wasted time reading your piece of junk above Select the Column of dates (for example Column A in my image above) Under the Data tab, select 'Text to Columns' (it's a button in the middle of the toolbar as shown in my image above) Select the Delimited option and hit the Next button Untick all the Delimiters, e.g. Tab, Semicolon, Comma, Space and hit the Next button Select the Column Data Format 'Date: MDY' and hit the Finish button. Right-click the highlighted column of dates (e.g. the data you've just converted) and open Format Cells to change the Date format into the style you need. For example DD/MM/YY, DD/MM/YYYY or whichever format is relevant such as DD-MMM-YY in the Custom section. NB the last part is >> in the Custom section << at the bottom of the various options there
This is what happens when trying to transform US dates into the serial number behind the date ... https://www.screencast.com/t/yxRaoS8Ws - as you can see - it stays the same date - no serial number. Suggestoins please?
I have wasted hours trying to get to the bottom of this - ridiculous you cannot set your csv file in such a way as to be able to sort it by date. Then you give this flimsy non-existent instruction above? Talk to me about Enjoy & feel the liberation ? Then give me no instrucitons to further this whatsoever. When dates are set into US format there seems to be NO way to budge them. The UK dates, I can even get to the pre-formated number per date, but not your US format - you have hard wired this such that forever my csv lists are broken in half and one half cannot be sorted. TOTALLY USELESS
Thank You
Could not LinkedIn offer the option to get your reports with the desired date format? >.<