Convert American date and time into Australian date and time

[barto1973]

Hi I have data with American date time and I need it in Australian date time. Examples below

8/02/2021 3:00 needs to be 2/8/2023 3:00 I dont what the time to change. Where its 3 am it needs to stay 3 am.
10/08/2021 13:30 needs to be 8/10/2023 13:00

Note the number of characters in the cell change (shown in above exmaples) . The date can start with 1 character or 2 characters depending on if the month is 1-9 or 10-12. Also the time has different number of characters depending on if its before or after 10am.

 

[BigBeachBananas]

10/08/2021 13:30 needs to be 8/10/2023 13:00

Why did 13:30 become 13:00?

 

[barto1973]

Why did 13:30 become 13:00?

Further clarification. Need an excel formula. The time needs to be unchanged so 13:30 stays as 13:30.

It just where is says 8/02/2023 my excel thinks its 8th Feb when in fact its 2nd August...

 

[BigBeachBananas]

Select one of the cells and press Ctrl+1.
Go to custom, in the Type dialog box, enter: dd/mm/yyyy hh:mm
Copy and paste the format to the other cells.

Edit: Note that this will convert the existing cell into Australian format, but you would still need to enter it in American format for new cells.

 

[barto1973]

I have sourced a solution

DATE(YEAR(E6),DAY(E6),MONTH(E6))+MOD(E6,1)

This has change the date from US to Aust (8th Feb to 2nd Aug) and kept the time unchanged.

 

[barto1973]

I have just encountered a further problem. Some data sent to me is text and others is 24hr format. I have the fix for 24hr format (see above) not need an alternate for the text format

In the example below I need a formula for the text date 3/13/22 12:00 AM. in the day column I want the date but converted to Aust format. So in example below I need just the date 13/03/2022 (13th March 2022). And in the time column I need a formula to get the text time recognised as a real time

 

[barto1973]

Any help for last post above please?