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

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
Asked:
maximyshka
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
are you sure you got the right data?
0
 
ry_ashpoolCommented:
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
 
ry_ashpoolCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Rey Obrero (Capricorn1)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
 
maximyshkaAuthor 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
 
Rey Obrero (Capricorn1)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
 
lynx20Commented:
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
 
maximyshkaAuthor Commented:
Thank you very much capricorn & ry_ashpool.  
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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