How to change multiple date formats in same column

**muchado** · 11-08-2010, 08:05 PM

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

**JBeaucaire** · 11-08-2010, 09:49 PM

Maybe this? I can't tell if you cells have text values in them, and if so, if that's an apostrophe in the cell or not. So:

=DATEVALUE(MID(F2,2,10))

You might change that 2 to 1.

**muchado** · 11-09-2010, 12:03 AM

Hi Jerry,

Sadly this isnt working. I don't have apostrophies in the field and Ive played around with the format also. I will keep trying.

I have attached a sample spreadsheet for anyone who wishes to look.

Many thanks,

Muchado

**JBeaucaire** · 11-09-2010, 03:04 AM

You didn't go down far enough. My formula works for B14:B16 because those cells ARE text strings, not date strings. Excel is showing you the different types of data by the way it flushes right numeric values (the real date strings) and flushes left text strings.

So, you really need two formulas. I put this in A2 and copied down:

=IF(ISTEXT(B2),DATEVALUE(MID(B2,1,10)), INT(B2))

...then formatted the results with a custom number format of mmm-yy.

**DonkeyOte** · 11-09-2010, 03:55 AM

If as implied by your initial attachment you're experiencing issues with US conversions and are indeed transposing month & day to get the correct date then:

Please Login or Register  to view this content.

**muchado** · 11-10-2010, 01:34 AM

Hi there,

I'm sorry I didnt mention the US format was combined with the text and date combination problem. I should have also prefaced being a bit of a novice to some degree.

I got a lot from both replies though! I think DonkeyOte you put it together so it worked on both issues. I didnt think of using the REPLACE function and certainly wouldnt have been able to incorporate it the way you have. But I do understand it and have learnt a lot from this IF statement.

I appreciate both your comments!!!

Thank you thank you,

Best regards,

Muchado