Format Date/Time

[brandon16]

Hi,
My CSV has different date/time values. I have tried to format the cells but the values aren't consistent. Any idea how to amend these please? Some years show 19 and other 2019, some values are left aligned and other are slightly to the right. I can't seem to make it all the same.

 

[jasonb75]

Are you using UK regional settings? (dd/mm/yy format)

If you are then your system will be misinterpreting the dates, anything where the day is on or before the 12th the day and month will be swapped (For example 01/05 will be read as Jan 5th instead of 1st May). When the day is on or after the 13th then it will be seen as a text string instead of a valid date, which is why you can not format them.

In order to correct this you will need to use Text to columns to convert them to the correct format. There may be other ways of doing this but I have found this to be the most reliable method.

Start with an unedited version of the csv file, if you have already been making changes then some of the dates may not convert correctly.

Insert an empty column to the immediate right of the date and time column. For this to work correctly, the date and time need to be separated.
Select the column of dates.
Go to the Data tab on the Excel ribbon.
Click Text to Columns.
Click Next once.
Tick the box for Space, the others should be unticked.
Click Next.
Change the Date dropdown to MDY.
Click Finish.

If all has gone according to plan then you should now have a consistent set of valid dates.

 

[brandon16]

Are you using UK regional settings? (dd/mm/yy format)

If you are then your system will be misinterpreting the dates, anything where the day is on or before the 12th the day and month will be swapped (For example 01/05 will be read as Jan 5th instead of 1st May). When the day is on or after the 13th then it will be seen as a text string instead of a valid date, which is why you can not format them.

In order to correct this you will need to use Text to columns to convert them to the correct format. There may be other ways of doing this but I have found this to be the most reliable method.

Start with an unedited version of the csv file, if you have already been making changes then some of the dates may not convert correctly.

Insert an empty column to the immediate right of the date and time column. For this to work correctly, the date and time need to be separated.
Select the column of dates.
Go to the Data tab on the Excel ribbon.
Click Text to Columns.
Click Next once.
Tick the box for Space, the others should be unticked.
Click Next.
Change the Date dropdown to MDY.
Click Finish.

If all has gone according to plan then you should now have a consistent set of valid dates.

That's brilliant, thank you so much for your help!