Sorting out dates

[vabosman]

Hi guys,

I need to load an excel file into SPSS but am running into some issues with the data format. I've attached the date samples in a separate file, I copied it down from a online survey database which got samples ranging from September 30th, 2015 to November 13th, 2015. However in the file, dates like (B1) 01/10/2015 are displayed correct in the cell but when I try to set the format to date it will turn into MM/DD/YYYY while I need DD/MM/YYYY. These specific cells, in which the days and months which can be correctly used interchangeably between the metric and imperial system, will unfortunately not load properly into SPSS as a result. Any date of the 13th or higher, such as 30/09/2015, works fine.

Any idea on how I can solve this without having to type out manually? Thank you in advance.

[Special-K]

Excel's date format gets it from Windows Locale, ie if your region is set to Europe it will be dd/mm/yyyy if it's set to USA then it will be mm/dd/yyyy.
Which suggests that you can't have a spreadsheet consisting of dd/mm/yyyy AND mm/dd/yyyy dates at the same time.

Use LEFT MID RIGHT forumlas to manipulate the parts of each date into the correct order.

[Aardigspook]

Welcome to the forum.

Looking at your file, the cells with the dates are formatted with three different formats:
- 'General' (which will display the date based on your regional settings)
- 'Date' (which will also display the date based on your regional settings)
- 'Custom' (which is set to mm dd yyyy, so will always display in this American format)

If you select all the data and format the cells as 'Date', you'll get all the dates in the date format specified in your regional settings, as Special-K as said. Excel is clever enough to realise that 10th day of 14th month cannot be correct, so a date of 14 10 2015 will always be understood as 14th October.
Where you run into problems is the ambiguous dates such as 04 05 2015. If you format this as a date and it comes out as 5th April when you know it should be 4th May, then you can use DATE to switch the day and month. The DATE function syntax is Date (Year, Month, Day) so if you give it the day from an incorrect date as the month, and the month as the day, that will swap the day and month around - for example, if the incorrect date is in B1, use this:

=DATE(YEAR(B1),DAY(B1),MONTH(B1))

Hope that helps.