Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Turn six digits into date 1

Status
Not open for further replies.

gizmo1973

MIS
Joined
Aug 4, 2004
Messages
2,828
Location
GB
Hi,

I have data like this

210305
220405
190504
210704

Which I cannot format as date!
How can I siwtch these from a number into a date? i.e.

21/03/05
22/04/05
19/05/04
21/07/04

Regards, Phil.

M.U.F.C. Show your true support here, it's still not too late:
"Where there's blame there's DPlank"
 
You can use Left$, Mid$ and Right$ to format your data as dates in dd/mm/yy format. For example, in an Access query you could create an expression:
Code:
Newdate: left$(OldDate,2) & "/" & mid$(OldDate,3,2) & "/" & right$(OldDate,2)
If OldDate is your original data in a table, the expression should display dd/mm/yy values which you can write to another field via an update query.

Here is a VBA fragment which does the same thing:
Code:
dim strOldDate as String
dim strNewDate as String

strOldDate = (load a data item here)
strNewDate = left$(strOldDate,2) & "/" & mid$(strOldDate,3,2) & "/" & right$(strOldDate,2)
You would use this if you are writing a 'conversion routine' , perhaps to read your data from a text file and write it out as dd/mm/yy data to another text file.

I hope this gives you some useful ideas.

Bob Stubbs
 
?Which product?
If it's XL then you can use:

=DATE(RIGHT(A1,2)+2000, MID(A1,3,2), LEFT(A1,2))

where A1 contains the original value
 
Sorry guys, my bad. It was XL2000
I solved it by text to columns, fixed width and then concatanated the number back together with a / seprator

Regards, Phil.

M.U.F.C. Show your true support here, it's still not too late:
"Where there's blame there's DPlank"
 
Phil, Text to Columns was the right way to go, but you should have done it without any breaks and then just selected the date format from the options listed on that single column of data. Then just apply whatever date format you wanted - No need to break it up at all.

Regards
Ken.................

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Ken - have a star for that one! I tried your technique, which worked fine. I also realised that it works even if the source data is not in the same date format as my local dates. For example:

I am in the United Kingdom, using dd/mm/yyyy as my local date format.

If I use gismo1973's original data, which is in UK ddmmyy format, and choose the dmy date format option at step 3 of the Text to Columns process, the dates convert correctly to 21/03/2005 etc.

If I set up some 'US-style' dates, e.g. 032105, 051904 etc, and choose the mdy option during Text to Columns, then these US-style dates also convert correctly to my UK dd/mm/yyyy format. Finally, this works even if the leading zero is missing (32105 instead of 032105).

This is a really neat technique - thanks for posting it!

Bob Stubbs (London, UK)
 
You're welcome :-)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 


FYI...

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

[glasses] [red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top