reformatting date result

I tried this but it dis not work
                f(i, 1) = CDATE(Mid(Fyle.Name, 8, 8)).NumberFormat = "m/d/yyyy"

Hello.
This seems to work
f(i, 1) = CDate(Mid(Fyle.Name, 8, 8))

But why not the previous one?
And if I want a different format how would I do it? It turns out that the one here gives the right format by chance.

No. I am wrong

f(i, 1) = CDate(Mid(Fyle.Name, 8, 8))

Does not work. Any other ideas?

Hello. I tried this

dStartTime = DateSerial("20" & Mid(Fyle.Name, 12, 2), Mid(Fyle.Name, 10, 2), Mid(Fyle.Name, 8, 2))

                f(i, 1) = dStartTime
                f(i, 2) = Fyle.Size / 1024

Select allOpen in new window

but I get an "application defined or user defined error" for
 Worksheets("mbPerFileFromPerl").Range("a2").Resize(i, 2) = f

sorry, I meant, "but I get an "application defined or object defined error"

I changed to this, which almost seems to work but not quite

 dStartTime = DateSerial("20" & Mid(Fyle.Name, 14, 2), Mid(Fyle.Name, 11, 2), Mid(Fyle.Name, 8, 2))
                
                f(i, 1) = Format(dStartTime, "mm/dd/yyyy")
                f(i, 2) = Fyle.Size / 1024
               

Select allOpen in new window

Some dates it works but some it also gives a time

6/13/2012
6/13/2012
6/13/2012
6/13/2012
6/13/2012
6/13/2012
6/12/2012 0:00
6/12/2012 0:00
6/12/2012 0:00
6/12/2012 0:00
6/12/2012 0:00

Thanks. Well I preformatted the first column with a custom date format and my code was working. Then I tried your update and that also works with the same date formatting. Which one should I use?

Have a look at this test vbs file:

Option Explicit

Dim Fyle_Name
Fyle_Name = "sweets_12-07-12_13-00-02.csv"

Msgbox _
	Mid(Fyle_Name, 8, 8) & vbCrLf & _
	Month(CDate(Mid(Fyle_Name, 8, 8))) & vbCrLf & _
	Mid(Fyle_Name, 11, 3) & Mid(Fyle_Name, 8, 3) & Mid(Fyle_Name, 14, 2) & vbCrLf & _
	Month(CDate(Mid(Fyle_Name, 11, 3) & Mid(Fyle_Name, 8, 3) & Mid(Fyle_Name, 14, 2)))

Select allOpen in new window

This shows month as expexted:However, change the first 12 in Fyle_Name to 13 and month is 'magically' equal to 7 in both cases!

So you mean in the last one it read the month from the first entry (the 07)?

Actually I think I forgot about the year... and being equal to 12 as well didn't help. This code is better:

Mid(Fyle.Name, 14, 2) & "-" & Mid(Fyle.Name, 11, 2) & "-" & Mid(Fyle.Name, 8, 2)

Select allOpen in new window

Now you can use your format() and it will show correct. To use it in a cell you may have to set the cell format as well but that is probably your default date format anyway.

EDIT: Well, if you replace the "-" with "/" you wouldn't need the format but that is something to be tested...

Do you mean this?

            For Each Fyle In objFSO.getfolder(MyFolder).Files
                i = i + 1
                
                'dStartTime = DateSerial("20" & Mid(Fyle.Name, 14, 2), Mid(Fyle.Name, 11, 2), Mid(Fyle.Name, 8, 2))
                'f(i, 1) = Mid(Fyle.Name, 11, 3) & Mid(Fyle.Name, 8, 3) & Mid(Fyle.Name, 14, 2)
                f(i, 1) = Mid(Fyle.Name, 14, 2) & "-" & Mid(Fyle.Name, 11, 2) & "-" & Mid(Fyle.Name, 8, 2)
                
                'f(i, 1) = Format(dStartTime, "mm/dd/yyyy")
                f(i, 2) = Fyle.Size / 1024
                
            Next

Select allOpen in new window

Sorry, I thought I refreshed each time before I posted but now I see that I have been rambling on without seeing your posts in between mine. Let me re-read them and I will get back to you in a moment.

A couple of things:
- setting the format of the column could be a good thing, but in my case when I set it to general, then after the macro it shows as "*yyyy-mm-dd", which for me would be fine. I just tried setting it to "mm/dd/yyyy" and then it stays the same so that's great.
- the 'format' of the date in memory and conversions that take place is a totally separate issue though, and yes: with that test vbs I'm pretty sure it shows an awful feature of VB in general, silently switching date parts when it looks a bit like a date.

This one works consistently for me:

f(i, 1) = CDate(Mid(Fyle.Name, 14, 2) & "/" & Mid(Fyle.Name, 11, 2) & "/" & Mid(Fyle.Name, 8, 2))

Select allOpen in new window

But note that there may still be a dependency on the language/date format settings. Manually prepending the century may help but we all know what happened last time...

We could actually use the help of an Excel guru here, in that code you posted, you assign a string to the array element, which gets converted to a date at the moment you copy the array into the worksheet.

In my code the CDate() already does the conversion before putting it in the worksheet. Also note I use a different separator in there.

Robert, thanks. So what works for you is:

f(i, 1) = CDate(Mid(Fyle.Name, 14, 2) & "/" & Mid(Fyle.Name, 11, 2) & "/" & Mid(Fyle.Name, 8, 2))

Select allOpen in new window

f(i, 1) = CDate(Mid(Fyle.Name, 14, 2) & "/" & Mid(Fyle.Name, 11, 2) & "/" & Mid(Fyle.Name, 8, 2))

Select allOpen in new window

instead of this:

dStartTime = DateSerial("20" & Mid(Fyle.Name, 14, 2), Mid(Fyle.Name, 11, 2), Mid(Fyle.Name, 8, 2))
                
 f(i, 1) = Format(dStartTime, "mm/dd/yyyy")

Select allOpen in new window

Also, where are you saying that
"it converted to a date at the moment you copy the array into the worksheet"?
here?

Worksheets("mbPerFileFromPerl").Range("a2").Resize(i, 2) = f

Select allOpen in new window

I don't see how that is getting converted to a date.
Thanks again.

thanks. Do you mean this?

Sub generateDatesAndMbFormbPerFileFromPerl()
    
    Dim objFSO      As Object
    Dim MyFolder    As String
    Dim FName       As String
   Dim dStartTime As Date
    Dim i           As Long
    Dim Fyle, f()
    
    'MyFolder = "C:\Users\Bill\Desktop\Tweets to Aug 19"            '<< adjust to suit
    MyFolder = UserForm1.TextBox5.Text
    
    If Right$(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
    
    If Len(Dir(MyFolder, vbDirectory)) Then
        FName = Dir(MyFolder & "*.csv")
        If Len(FName) Then
            
            Set objFSO = CreateObject("scripting.filesystemobject")
            ReDim f(1 To objFSO.getfolder(MyFolder).Files.Count, 1 To 2)
            
            For Each Fyle In objFSO.getfolder(MyFolder).Files
                i = i + 1
                
                dStartTime = DateSerial("20" & Mid(Fyle.Name, 14, 2), Mid(Fyle.Name, 11, 2), Mid(Fyle.Name, 8, 2))
                
                f(i, 1) = Format(dStartTime, "mm/dd/yyyy")
                f(i, 2) = Fyle.Size / 1024
                
            Next
            
        End If
        
        If i Then
            Worksheets("mbPerFileFromPerl").Range("a1:b1") = [{"Dates", "Mb"}]
            Worksheets("mbPerFileFromPerl").Range("a2").Resize(i, 2) = f
			
			With Worksheets("mbPerFileFromPerl").Range("a2").Resize(i)
				.TextToColumns Destination:=.Cells(1), DataType:=1, FieldInfo:=Array(1, 3)
			End With
        End If
    End If

End Sub

Select allOpen in new window

and keeping this there:

                dStartTime = DateSerial("20" & Mid(Fyle.Name, 14, 2), Mid(Fyle.Name, 11, 2), Mid(Fyle.Name, 8, 2))
                
                f(i, 1) = Format(dStartTime, "mm/dd/yyyy")
                f(i, 2) = Fyle.Size / 1024

Select allOpen in new window

Both strings should work fine. Any difference may be in the dependency on local date settings, I'm not 100% sure. The DateSerial should be ok because it has a fixed order for year, month, day (didn't test that actually but seems fine). Realize that with Format() you convert it to a string.

I don't see how that is getting converted to a date

when I look at the actual value in the cell it's a number representing a date.

Hi

like this

Sub generateDatesAndMbFormbPerFileFromPerl()
    
    Dim objFSO      As Object
    Dim MyFolder    As String
    Dim FName       As String
    Dim i           As Long
    Dim Fyle        As Object
    Dim f()
    
    'MyFolder = "C:\Users\Bill\Desktop\Tweets to Aug 19"            '<< adjust to suit
    MyFolder = UserForm1.TextBox5.Text
    
    If Right$(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
    
    If Len(Dir(MyFolder, vbDirectory)) Then
        FName = Dir(MyFolder & "*.csv")
        If Len(FName) Then
            
            Set objFSO = CreateObject("scripting.filesystemobject")
            ReDim f(1 To objFSO.getfolder(MyFolder).Files.Count, 1 To 2)
            
            For Each Fyle In objFSO.getfolder(MyFolder).Files
                i = i + 1
                f(i, 1) = Mid(Fyle.Name, 8, 8)
                f(i, 2) = Fyle.Size / 1024
            Next
        End If
        
        If i Then
            Worksheets("mbPerFileFromPerl").Range("a1:b1") = [{"Dates", "Mb"}]
            Worksheets("mbPerFileFromPerl").Range("a2").Resize(i, 2) = f
            With Worksheets("mbPerFileFromPerl").Range("a2").Resize(i)
                .TextToColumns Destination:=.Cells(1), DataType:=1, FieldInfo:=Array(1, 3)
            End With
        End If
    End If

End Sub

Select allOpen in new window

Kris

Hello. I tried and I get half in one format and half in another. For example:


13-06-12
13-06-12
13-06-12
13-06-12
12/6/2012
12/6/2012
12/6/2012




1/7/2012
1/7/2012
1/7/2012
30-06-12
30-06-12
30-06-12


13-07-12
13-07-12
13-07-12
12/7/2012
12/7/2012
12/7/2012


and so on.

Hi

does it show correct format by adding this line after .TextToColumns... line ?

.NumberFormat = "mmm-dd-yyyy"

Select allOpen in new window

Kris

Please specify what you tried exactly because with several conversations going on at the same time it's hard to know which suggestion you're responding to. Also check the format settings in Excel, are they still the way you set them earlier (either custom format or your default date format).

Here's the file I've been using for testing, note I didn't use a userform to run the macro.

Interesting to see how the output changes when you do Ctrl-A, Ctrl-1 and set format to General or Text (for all cells) and then run the macro.
test2post.xls

Hello. Thanks Kris, I just tried your last two suggestions and I got


13-08-12
13-08-12
13-08-12
13-08-12
Dec-08-2012
Dec-08-2012
Dec-08-2012
Dec-08-2012
 

but my data reflects current time and so Dec does not exist in my data

Hi

Reaplce

Array(1,3)

with

Array(1,4)

Kris

Hi. that seems to work. The date format is 05-01-2012
whereas I expected 05/01/2012. Any suggestions on making that small change in the code?