american/australian dates

[orsm6]

Hi all - not sure how to tackle this one. I dump some data into a sheet for a date range in any given week that is only ever 5 days maximum. When the report is extracted it comes out in American date format (unable to be changed as far as I can tell)

I need to be able to use the dates in formulas in excel, but the issue I face right now is that:
- 02/05/2024 is showing up in excel as 2nd June 2024
- 02/20/2024 pretty sure also is not going to show the right date


The report pasted could be 1000 lines, so if possible it would be great to avoid a helper column.

looking for advice and a solution f anyone has one.

TIA

EDIT: cannot rely on text to columns or anything like that as the users of the file have extremely limited skills. so preference is to use formulas.

 

[MARK858]

02/05/2024 is showing up in excel as 2nd June 2024

Not sure why it would show that rather than 2nd May 2024 but try

• Select the column
• Data tab
• Text to Columns
• Delimited
• Next
• Make sure all the checkboxes are cleared
• Next
• Select Date checkbox
• Select the MDY option (yes MDY as you want the source format)
• Click Finish
• Format the cells as desired

 

[orsm6]

Not sure why it would show that rather than 2nd May 2024 but try

• Select the column
• Data tab
• Text to Columns
• Delimited
• Next
• Make sure all the checkboxes are cleared
• Next
• Select Date checkbox
• Select the MDY option (yes MDY as you want the source format)
• Click Finish
• Format the cells as desired

Thanks Mark - as mentioned I need a way of doing this by formula.

The users don't have the experience to do it as you suggested each time they populate the file

Also - yes - this one does show as MAY.. apologies...

if it says 02/06/2024 it says JUNE

 

[MARK858]

The users don't have the experience to do it as you suggested each time they populate the file

It isn't that hard or complicated when you try it but if you insist on a formula please post some sample data to test on (about a dozen lines) using XL2BB (and get the cell references you are using)

 

[Alex Blakenburg]

Since you have 365 and want to use a formula, see if this works for you:

testprojprotect.xlsm
	A	B
1	Imported Date	Date dd/mm/yyyy
2	2/05/2024	5/02/2024
3	02/20/2024	20/02/2024
HideThis

Cell Formulas
Range	Formula
B2:B3	B2	=LET(sDt,TEXT(A2,"dd/mm/yyyy"), aDt,TEXTSPLIT(sDt,"/"), DATE(INDEX(aDt,,3),INDEX(aDt,,1),INDEX(aDt,,2)))

 

[orsm6]

Since you have 365 and want to use a formula, see if this works for you:

testprojprotect.xlsm
	A	B
1	Imported Date	Date dd/mm/yyyy
2	2/05/2024	5/02/2024
3	02/20/2024	20/02/2024
HideThis

Cell Formulas
Range	Formula
B2:B3	B2	=LET(sDt,TEXT(A2,"dd/mm/yyyy"), aDt,TEXTSPLIT(sDt,"/"), DATE(INDEX(aDt,,3),INDEX(aDt,,1),INDEX(aDt,,2)))

Thanks Alex - exactly as needed.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.