Convert american date to Australian date (data in text format)

[Barto]

Hi,

I am having to load data into excel that has a date column. I have these challenges with converting the date from American format to Australian format (e.g. 4/2/20 to 4/2/20)
1. its in text format
2. number of characters are not consistent. For example the 2nd April shows as 4/2/20 (6 characters) and 10th April is 4/10/20 (7 characters) and 10th Oct is 10/10/20 (8 characters) so there is no consistency in characters.
3. its one data dump. Text to column is not an option. This is the first column of a data table.

Any formula out there to resolve this?

 

[powerhouse pickpocket]

Are you, or could you use a macro to manipulate your data?

In the interim, you could try this formula if you definitely want a formula approach.

20 04 03.xlsm
	A	B
1	Date US	Date AUS
2	4/2/20	2/04/2020
3	4/18/20	18/04/2020
4	10/10/20	10/10/2020
Date swap

Cell Formulas
Range	Formula
B2:B4	B2	=DATEVALUE(MID(SUBSTITUTE(A2,"/","/"&LEFT(A2,FIND("/",A2)),2),FIND("/",A2)+1,10))

Thank you this helped me converting with US format dates supplied as Text.

 

[Peter_SSs]

Thank you this helped me converting with US format dates supplied as Text.

Glad it helped, and "Welcome to the MrExcel board!"

 

[tommytiger]

Are you, or could you use a macro to manipulate your data?

In the interim, you could try this formula if you definitely want a formula approach.

20 04 03.xlsm
	A	B
1	Date US	Date AUS
2	4/2/20	2/04/2020
3	4/18/20	18/04/2020
4	10/10/20	10/10/2020
Date swap

Cell Formulas
Range	Formula
B2:B4	B2	=DATEVALUE(MID(SUBSTITUTE(A2,"/","/"&LEFT(A2,FIND("/",A2)),2),FIND("/",A2)+1,10))

Hi Peter, Thanks for this! Looks like you got 2 in 2 days - this also helped me with same issue!

I have another issue. A additional extract where the data comes through as datetime format but because the date is in american format excel recognises the incorrect date.
For example 4/03/2020 1:00:00 AM. This is actually 3rd April but my excel thinks its 4th March because I'm an Australian excel user.
I have tried modifying the previous formula provided but because in this instance its not text is does work.
Any idea on a formula solution here

Interval Start

4/03/2020 1:00​

4/04/2020 1:00​

4/05/2020 1:00​

Hi Barto, in case you haven't resolved your query yet, I managed to decode the time from my data using the formula:

=TIMEVALUE(RIGHT(A6,5))

mine was also formatted similar to yours so my actual formula combining date (using Peter's formula) and time was:

=DATEVALUE(MID(SUBSTITUTE(A6,"/","/"&LEFT(A6,FIND("/",A6)),2),FIND("/",A6)+1,10))+TIMEVALUE(RIGHT(A6,5))

If your data is formatted as date already though this doesn't work - needs to be formatted as text.

 

[Peter_SSs]

Hi Peter, Thanks for this! Looks like you got 2 in 2 days - this also helped me with same issue!

Cheers.
Welcome to you too!