Solved

# Convert text to date

Posted on 2007-10-16
1,073 Views
How can I convert Excel Date "1080303"--date displayed here "yy/mm/dd"

to date in format "mm/dd/yy" using any Excel or Access functions.  Access preferable

Cdate not working.
0
Question by:maximyshka

LVL 119

Expert Comment

are you sure you got the right data?
0

LVL 3

Expert Comment

In a cell or in VBA?

= date("20" & mid(a1,2,2),mid(a1,4,2),right(a1,2))

And then just format the cell accordingly ( date or custom yy/mm/dd)

0

LVL 3

Assisted Solution

That is assuming 1080303 really means 03/03/08 (mm/dd/yy)

The number 1080303 is not a native excel date, well at least in excel2003 anyway.
0

LVL 119

Expert Comment

is the value for date 03/08/03 in the format of yy/mm/dd

my guess is that, it is a long integer value. you will need a function to convert that.
pls answer the question and i will give you the function to convert the value to date
0

Author Comment

Regarding Data.  Yes, that's stupid way how Excel gets data.  I believe they got data probably from Mainframe.
Basically, you saying that first "1" does not mean anything?

0

LVL 119

Accepted Solution

try this function

Function LngInt2Date(ival As Variant) As Long
Dim sDate As String
Dim iYr As Integer
Dim iMon As Integer
Dim iDay As Integer

If IsNull(ival) Or Val(ival) = 0 Then Exit Function

sDate = Right("0" & Trim(Str(ival)), 6)
If Val(Right(sDate, 2)) < 50 Then
iYr = 2000 + Val(Right(sDate, 2))
Else
iYr = 1900 + Val(Right(sDate, 2))
End If

iDay = Val(Mid(sDate, 3, 2))
iMon = Val(Left(sDate, 2))

LngInt2Date = DateSerial(iYr, iMon, iDay)

End Function

if you want the formatting use it this way

format(lngint2date(1080303),"yy/mm/dd")

0

LVL 9

Expert Comment

Hi:

To expand upon the 108:

Many systems use the syntex of number of years since 1900 when creating a date serial number.  I ran into this with java script in web browsers back in 2003.  I had to either test which browser first or use long date coding.

Bob
0

Author Comment

Thank you very much capricorn & ry_ashpool.
0

## Join & Write a Comment Already a member? Login.

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

#### 729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!