Converting non-standard dates

I'm having a nightmare.

The background is that I have an excel table containing audit data. The date row contains dates either in mm/dd/yyyy or mm-dd-yyyy.

I want them all to be dd/mm/yyyy (I am in the UK and the date is in US format). I've tried =Text(_,"dd/mm/yyyy) which converts the forward slash dates / fine, but nothing I do works on the hyphen - dates. Even substituting the - for a / creates errors.

Can anyone give me a formula that will convert it all in one go?

Thanks in advance :-)

Jemma

Hi Shukla,

Thanks for the formula - it's worked on the date values which were mm-dd-yyyy but the ones which were mm/dd/yyyy (so actually fine and just needed changing round via date format functionality) now have a #VALUE! error. Ideally I am after a formula which works across the whole column as I am trying to automate a process.
Would some sort of iferror addition to the formula work perhaps?

Thanks,
Jemma

I've attached the sample data. So there are two date formats in row B.
I've inserted date C, into which I would like a standard date format based on row B data.
I would like whatever formula I use to standardise the dates, to work across the whole column.

Many thanks,
Jemma

Hi Jemma,

Opening in my UK Excel it changed the "/" dates into an actual date number, then displays as UK format. I'm not sure if this formula will work for the original data but feel free to give it a try:
=DATE(RIGHT(TEXT([@[Date Audited]],"dd-mm-yyyy"),4),LEFT(TEXT([@[Date Audited]],"dd-mm-yyyy"),2),MID(TEXT([@[Date Audited]],"dd-mm-yyyy"),4,2))

Ideally you will want to change the dates around whilst they are still text in the original file, before you put them into Excel and it tries to change them to date serial numbers.

Stormin'

It kinda works, the date are all in the right format, but as per the attached it's converted the dates that were in UK format (dd/mm/yyyy) into a completely other date - 2018 and 2019 because it's confused and thinks 13/12/2016 is the 13th month so Jan 2017 and the 12 is the date.
So, i'm thinking - can we include an =IF( function or something similar so that your formula only operates on the mm-dd-yyyy dates and simply copies over the value from the dd/mm/yyyy dates?

Yeah the problem here is that when I open the file, Excel sees the date strings with "/" in them as dates and converts them for me (yay).

Try:
=IF(IFERROR(FIND("-",TEXT([@[Date Audited]],"@")),0),DATE(RIGHT(TEXT([@[Date Audited]],"@"),4),LEFT(TEXT([@[Date Audited]],"@"),2),MID(TEXT([@[Date Audited]],"@"),4,2)),DATEVALUE([@[Date Audited]]))

Stormin'

Your formula worked () I had to change DATEVALUE to VALUE for the IFERROR argument to work. You've been really helpful. Thank you so much!!

Jemma

You're welcome

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.