David Thacker Blog

Today I have been pulling my hair trying to generate a simple report in either excel or google sheets. I was getting the same issue in both. The issue was the date was being recognized in some cells. but not others in the same column.  First I thought this was an export issue but opening the file in a text editor quickly but the breaks on blaming the system I was exporting from.  So I tried different date and time formats, using the format function and it was changing some cells and not others. In the end it turned out to be the language from my applications are set to English UK, I switched it to English USA and everything works perfectly. The strange thing is that the dates it was recognizing it was outputting in the correct USA format like the other dates on the sheet and not interpreting them as UK dates.  Anyway maybe this will help somebody in the future. 

Quite often you will have a formatting issue in excel either it does not understand the format or you have extra data in a column you dont need. Before it will understand what you want.  ( A side note I do not remember it being this difficult on windows, but on Mac this is the solution I found).   In my case this is the data : Apr 6 2018 12:06:21:000AM its simply a date plus a time but excel does not understand it yet. In this example I just want the date formatted correctly and I will get rid of the time. Separate out the data into individual columns Hi-light the column and choose 'Text to Columns' Choose delimited Space delimited Finish Now they are separated into the 4 columns, we can now delete the time in the last column. We now need to combine the data back into 1 column - we can do this by using a formula like this : =M2&"/ "&N2&"/ "&O2 this now gives us Apr/ 6/ 2018 but excel does not understand its a date, even if you ...