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?

 

[Peter_SSs]

Text to column is not an option

Why do you say that?

Select the column
Text to Columns
Delimited
Next
Make sure / is not entered in the 'Other' box
Next
Select MDY in the Date drop-Down
Finish

 

[Barto]

Because I'm building an interim report where I'm dumping data each day and dont want to have to do rext to columns everytime i dump data.

The data has 14 columns (A-O).... some columns with merge cells (very messy). Column A has the date. So looking for a formula option I can put in column P that looks at the date and converts it.

I have no admin control on the data given to me and will need to work with what I'm given for 3-6mths.

Formula solutions only please

 

[Macropod]

Presumably, any dates where the day is 13 or greater are already correctly rendered. That said, the following macro converts date strings and values in a selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.

Sub ConvertDateFormat()
Dim DtRange As Range, oCell As Range, oTxt As String
If Selection.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = oCell.Text
If UBound(Split(oTxt, "/")) = 2 Then _
oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

 

[Peter_SSs]

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))

 

[Barto]

Really hoping for a formula solution please

 

[Barto]

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 looks like this works

 

[Peter_SSs]

Thank you looks like this works

You're welcome. Post back if any complications arise.

 

[Barto]

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,

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​

 

[Peter_SSs]

Any idea on a formula solution here

Which results are you wanting, column B or C?

20 04 06.xlsm
	A	B	C
1	4/03/2020 1:00	3/04/2020	3/04/2020 1:00
2	4/04/2020 1:00	4/04/2020	4/04/2020 1:00
3	4/15/2020 1:00	15/04/2020	15/04/2020 1:00
Sheet2 (2)