Date string to Date

[Gismo]

I used the formula as above the result is still the same, and both my report and the spreadsheet is in the same format

Mid([Installation Date1],3) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)
View attachment 86134

how do i attach a xls file?

 

[Minty]

I used the formula as above the result is still the same, and both my report and the spreadsheet is in the same format

Mid([Installation Date1],3) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)
View attachment 86134

You are still missing the 2

Mid([Installation Date1],3,2) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)

 

[Gismo]

You are still missing the 2

Mid([Installation Date1],3,2) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)

sorry, finger trouble
it still gives incorrect result

 

[Minty]

Okay two functions for the price of one...

FLibble	UKDate	SQLDate
220120	22/01/20	01/22/20
250918	25/09/18	09/25/18
140120	14/01/20	01/14/20
301119	30/11/19	11/30/19
220120	22/01/20	01/22/20
10120	01/01/20	01/01/20
271119	27/11/19	11/27/19
200120	20/01/20	01/20/20

IIf(Len([Flibble])>5,Left([Flibble],2) & "/" & Mid([Flibble],3,2) & "/" & Right([Flibble],2),"0" & Left([Flibble],1) & "/" & Mid([Flibble],2,2) & "/" & Right([Flibble],2)) AS UKDate

IIf(Len([Flibble])>5,Mid([Flibble],3,2) & "/" & Left([Flibble],2) & "/" & Right([Flibble],2),Mid([Flibble],2,2) & "/" & "0" & Left([Flibble],1) & "/" & Right([Flibble],2)) AS SQLDate

Replace Flibble with your date field name

 

[Gismo]

Okay two functions for the price of one...

FLibble	UKDate	SQLDate
220120	22/01/20	01/22/20
250918	25/09/18	09/25/18
140120	14/01/20	01/14/20
301119	30/11/19	11/30/19
220120	22/01/20	01/22/20
10120	01/01/20	01/01/20
271119	27/11/19	11/27/19
200120	20/01/20	01/20/20

IIf(Len([Flibble])>5,Left([Flibble],2) & "/" & Mid([Flibble],3,2) & "/" & Right([Flibble],2),"0" & Left([Flibble],1) & "/" & Mid([Flibble],2,2) & "/" & Right([Flibble],2)) AS UKDate

IIf(Len([Flibble])>5,Mid([Flibble],3,2) & "/" & Left([Flibble],2) & "/" & Right([Flibble],2),Mid([Flibble],2,2) & "/" & "0" & Left([Flibble],1) & "/" & Right([Flibble],2)) AS SQLDate

Replace Flibble with your date field name

can i use your code in a query as is?

IIf(Len( [Installation Date1] )>5, Left( [Installation Date1] ,2) & "/" & Mid( [Installation Date1] ,3,2) & "/" & Right( [Installation Date1] ,2),"0" & Left( [Installation Date1] ,1) & "/" & Mid( [Installation Date1] ,2,2) & "/" & Right( [Installation Date1] ,2)) AS UKDate

 

[Minty]

You should be able to. Here is the original query that gave me the results;

SELECT Table3.FLibble, IIf(Len([Flibble])>5,Left([Flibble],2) & "/" & Mid([Flibble],3,2) & "/" & Right([Flibble],2),"0" & Left([Flibble],1) & "/" & Mid([Flibble],2,2) & "/" & Right([Flibble],2)) AS UKDate, IIf(Len([Flibble])>5,Mid([Flibble],3,2) & "/" & Left([Flibble],2) & "/" & Right([Flibble],2),Mid([Flibble],2,2) & "/" & "0" & Left([Flibble],1) & "/" & Right([Flibble],2)) AS SQLDate
FROM Table3
WHERE (((Table3.FLibble) Is Not Null));

My source table was Table3, replace as necessary

 

[Gismo]

You should be able to. Here is the original query that gave me the results;

SELECT Table3.FLibble, IIf(Len([Flibble])>5,Left([Flibble],2) & "/" & Mid([Flibble],3,2) & "/" & Right([Flibble],2),"0" & Left([Flibble],1) & "/" & Mid([Flibble],2,2) & "/" & Right([Flibble],2)) AS UKDate, IIf(Len([Flibble])>5,Mid([Flibble],3,2) & "/" & Left([Flibble],2) & "/" & Right([Flibble],2),Mid([Flibble],2,2) & "/" & "0" & Left([Flibble],1) & "/" & Right([Flibble],2)) AS SQLDate
FROM Table3
WHERE (((Table3.FLibble) Is Not Null));

My source table was Table3, replace as necessary

so far so good, seems to be working
thank you

 

[CJ_London]

for the future, one way to avoid your instances of finger trouble and the resulting confusion is to copy/paste your actual code. It will be quicker too.