CODEWITHSUNDEEP
vbadatetimeexcel
schnarr
schnarr

Reputation: 29

Date format dd:mm:yy:hh:mm:ss to a dd/mm/yy hh:mm:ss

I want to use VBA to convert the date stored on a file in the format dd:mm:yy:hh:mm:ss to something usable for sorting in excel using VBA to process files. I can't find anything that is compact. I have tried text to columns and then combine the values separated out back into a date and a time but this is cumbersome and slow for big data files.

Anyone know a quick method that can be used??

Upvotes: 1

Views: 4872

Answers (6)

Sorceri
Sorceri

Reputation: 8033

You can use the split function and parse out the string since you will (assuming here) have dd:mm:yy:hh:mm:ss so first three are the date and last three are the time.

Upvotes: 0

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

Here's a simple VBA function:

Function ConvertDateFormat(dateInOldFormat As String)
    Dim d As Date
    Dim ss() As String
    ss = Split(dateInOldFormat, ":")
    d = DateSerial(CInt(ss(2)), CInt(ss(1)), CInt(ss(0))) _
        + TimeSerial(CInt(ss(3)), CInt(ss(4)), CInt(ss(5)))
    ConvertDateFormat = Format(d, "dd/mm/yy hh:mm:ss")
End Function

Usage:

s = ConvertDateFormat("30:08:12:08:52:11")
'returns "30/08/12 08:52:11"

Here I'm not even going to consider dealing with Y2K issues... Internally in this function I'm assuming that year "12" means 12 AD, but this makes no difference in the end result which has a 2-digit year anyway.

Upvotes: -1

yosukesabai
yosukesabai

Reputation: 6244

a method using only Excel formula, no VBA

=DATE(2000+MID(A1,7,2),MID(A1,4,2),LEFT(A1,2))+TIMEVALUE(RIGHT(A1,8))

I assumed that, for example, 1am is expressed as 01:00:00, i.e. not dropping reading zero anywhere, always two characters for each of 6 elements.

Upvotes: 0

lori_m
lori_m

Reputation: 5567

Maybe this to convert dates in the range A1:A10:

[A1:A10]=[IF({1},TEXT(SUBSTITUTE(A1:A10,":",""),"00\/00\/00  00\:00\:00"))]

Upvotes: 2

brettdj
brettdj

Reputation: 55682

A quick way You could use the code below to

  1. Read in the DateLastModified of all the files in a certain folder into an array
  2. Dump and sort the array in a temporary worksheet
  3. Store the sorted array (in a variable Y below)

Code

Sub GetFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strFolder As String
Dim strFileName As String
Dim lngCnt As Long
Dim X()
Dim Y
Dim ws As Worksheet

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

strFolder = "c:\temp"
strFileName = Dir(strFolder & "\*.*")

Set objFSO = CreateObject("scripting.filesystemobject")
Set objFolder = objFSO.getfolder(strFolder)

ReDim X(1 To objFolder.Files.Count, 1 To 2)

For Each objFile In objFolder.Files
lngCnt = lngCnt + 1
X(lngCnt, 1) = objFile.Name
X(lngCnt, 2) = objFile.datelastmodified
Next

Set ws = Sheets.Add
ws.[a1].Resize(UBound(X, 1), 2) = X
ws.Range("A:B").Sort ws.Range("B1"), xlDescending
Y = ws.[a1].Resize(UBound(X, 1), 2)
ws.Delete

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub

Upvotes: 0

user857521
user857521

Reputation:

How about the following

Option Explicit

Sub test()
Dim dtStr As String
Dim dtArr() As String
Dim rebuildDt As String
Dim dtDte As Date
Dim dtStr2 As String


dtStr = "29:08:12:23:37:04"
dtArr = Split(dtStr, ":")
rebuildDt = dtArr(0) & "/" & dtArr(1) & "/" & dtArr(2) _
    & " " & dtArr(3) & ":" & dtArr(4) & ":" & dtArr(5)

dtDte = CDate(rebuildDt)
dtStr2 = Format(dtDte, "dd/mm/yy hh:mm:ss")
Debug.Print dtStr2

End Sub

Upvotes: 1

Related Questions