• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1141

# Convert text to date

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
maximyshka
• 3
• 2
• 2
• +1
2 Solutions

Commented:
are you sure you got the right data?
0

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

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

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

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

Commented:
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 Commented:
Thank you very much capricorn & ry_ashpool.
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.