Date string to Date (1 Viewer)

namliam

The Mailman - AWF VIP
Local time
Today, 14:09
Joined
Aug 11, 2003
Messages
11,696
maybe you could post the actual spreadsheet. Your post #7 should have worked.
Concure...

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

In order for you to get 1119/11/19 you must have not used the above formula....
instead...
Expr4: Mid([Installation Date1],3) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)

Omitting the ",2" is the only logical problem and easy to make that mistake without any error.... (IIRC)
 

Gismo

Registered User.
Local time
Today, 15:09
Joined
Jun 12, 2017
Messages
1,298
Concure...

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

In order for you to get 1119/11/19 you must have not used the above formula....
instead...
Expr4: Mid([Installation Date1],3) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)

Omitting the ",2" is the only logical problem and easy to make that mistake without any error.... (IIRC)
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)
1603787391060.png
 

Gismo

Registered User.
Local time
Today, 15:09
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 13:09
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 15:09
Joined
Jun 12, 2017
Messages
1,298
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

1603790487542.png

1603790524314.png
 

Minty

AWF VIP
Local time
Today, 13:09
Joined
Jul 26, 2013
Messages
10,355
Okay two functions for the price of one...


FLibbleUKDateSQLDate
22012022/01/2001/22/20
25091825/09/1809/25/18
14012014/01/2001/14/20
30111930/11/1911/30/19
22012022/01/2001/22/20
1012001/01/2001/01/20
27111927/11/1911/27/19
20012020/01/2001/20/20


Code:
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

Registered User.
Local time
Today, 15:09
Joined
Jun 12, 2017
Messages
1,298
Okay two functions for the price of one...


FLibbleUKDateSQLDate
22012022/01/2001/22/20
25091825/09/1809/25/18
14012014/01/2001/14/20
30111930/11/1911/30/19
22012022/01/2001/22/20
1012001/01/2001/01/20
27111927/11/1911/27/19
20012020/01/2001/20/20


Code:
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

1603796087739.png

1603796106587.png
 

Minty

AWF VIP
Local time
Today, 13:09
Joined
Jul 26, 2013
Messages
10,355
You should be able to. Here is the original query that gave me the results;
SQL:
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

Registered User.
Local time
Today, 15:09
Joined
Jun 12, 2017
Messages
1,298
You should be able to. Here is the original query that gave me the results;
SQL:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2002
Messages
42,981
It's Tuesday. You could have had your answer on Friday with a lot less aggravation all around.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2013
Messages
16,555
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.
 

Users who are viewing this thread

Top Bottom