Multiple date formats to 1

**somesoldiers** · 07-29-2009, 10:11 AM

hi All

I have a sheet with 2 different date formats
5/29/2009 9:00:02 AM
29/05/2009 09:00:01

is it possible to create a formula that will remove the time stamp and change all dates to dd/mm/yyyy?

I got as far as

=MONTH(I2)&"/"&DAY(I2)&"/"&YEAR(I2)

this works for type 1 but returns an error for type 2, I tried switching the DAY and MONTH in the formula but am still getting the error

Thanks all

Noel

**Palmetto** · 07-29-2009, 10:50 AM

This worked for me:

=LEFT(A2,5)*1

Since the date portion of the cell value is only 5 characters, this pulls out the 5 numeric characters comprising the date portion.

**somesoldiers** · 07-29-2009, 11:50 AM

Thanks Palmetto,

I had tried that but was using 10 instead of 5.
I am still getting a VALUE error for UK dates formats though, any ideas?

thanks

Noel

**Palmetto** · 07-29-2009, 11:57 AM

The only thing I can think of is the date is not a valid date.

Date are actually a numeric value in Excel and as such are automaticlly right aligned in the cell per the default format of General.

If your cell format is General and the date is not aligned to the right (widen the column to exagerate the effect), then you have an invalid date.

**DonkeyOte** · 07-29-2009, 12:01 PM

Noel, what regionals are you running yourself - for me in the UK I would expect the first date format to be acceptable but the 2nd not so... you're implying otherwise (ie on US settings)

The problem you have is put simply that 29/5 in US style doesn't exist - ie there is no month 29... for those entries not numeric you need to switch the fields around... for those that are appearing as text if they are always dd/mm/yyyy you could perhaps use:

=INT(IF(ISNUMBER(A2),A2,MID(A2,4,2)&"/"&REPLACE(A2,4,3,"")))

**somesoldiers** · 07-29-2009, 12:01 PM

no is not aligned to the right, will have to get the dates valid, thanks for the tip

**martindwilson** · 07-29-2009, 12:16 PM

i think You need to seperale then as how could you knoW what 3/4/09 means
assuming all UK in 01/01/2009 format and all US have 1/01/2009 00:00:00 AM/PM format
THEN
=IF(OR(RIGHT(A1,2)={"AM","PM"}),(MID(A1,FIND("/",A1)+1,2)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&MID(A1,FIND("/",A1)+4,4))+0,LEFT(A1,10)+0)