Hi there,

I have a report which is driven by data I don't have control over. I want to add a column in excel to change the date format to month and year only, however i have two types of formats in the one date column, which complicates things.

I have one date that appears like '11/05/2010 10:54:58'
The cell reads '11/05/2010 10:54:58 AM' and has two spaces between date and time but appears without the AM or PM.

Another date appears like '10/28/2010 1:24:07 PM'
The cell reads '10/28/2010 1:24:07 PM' and has only one space between date and time and appears with AM/PM.

The below formula works fine for the 1st date but doesnt work for the 2nd type of date format.
=(DATE(YEAR(B2),DAY(B2),MONTH(B2)))

Do I also need to use an IF statement to tell it to use one formula if the other doesnt work??

Kind Regards,

Muchado