Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

reformatting date result

Posted on 2012-08-29
27
Medium Priority
?
275 Views
Last Modified: 2012-09-28
Hello. Someone suggested this macro for getting the file sizes in a folder with files that have names like
sweets_01-07-12_13-00-02.csv
 
 Sub generateDatesAndMb()
    
    Dim objFSO      As Object
    Dim MyFolder    As String
    Dim FName       As String
    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
                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
        End If
    End If

End Sub

Open in new window


It works great but I want to reformat the first column of output which is a date. It currently generates these two formats (I am not sure why part of the output has the first format and part the second)
13-07-12
12/7/12


I need the date to be in this format:
7/1/2012
7/26/2012

Any ideas?
Thanks.
0
Comment
Question by:willie108
  • 14
  • 8
  • 5
27 Comments
 

Author Comment

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

Author Comment

by:willie108
ID: 38348752
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.
0
 

Author Comment

by:willie108
ID: 38348771
No. I am wrong

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

Does not work. Any other ideas?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:willie108
ID: 38348927
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

Open in new window

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

Author Comment

by:willie108
ID: 38348931
sorry, I meant, "but I get an "application defined or object defined error"
0
 

Author Comment

by:willie108
ID: 38348987
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
               

Open 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
0
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 668 total points
ID: 38349137
The difference between 6/12 and 6/13 triggers a memory for me: it's a very bad VB thingy I think that if both the day and the month are 12 or less it can be interpreted 'the wrong way around', but when one of them is higher then the other must be the month and it is silently converted!

Now the big question is what is 'the wrong way around'? You want month/day so on most systems you're golden because that's often the default. However, in the excel file and the format that's being used it could be the other way around.

Can you try changing line 23 in the posted code to:
f(i, 1) = Mid(Fyle.Name, 11, 3) & Mid(Fyle.Name, 8, 3) & Mid(Fyle.Name, 14, 2)

Open in new window

Another possibility is using a more specific conversion, see for example: http://msdn.microsoft.com/en-us/library/aa227484(v=vs.60).aspx
0
 

Author Comment

by:willie108
ID: 38349168
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?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349186
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)))

Open in new window

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

Author Comment

by:willie108
ID: 38349194
So you mean in the last one it read the month from the first entry (the 07)?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349200
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)

Open 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...
0
 

Author Comment

by:willie108
ID: 38349216
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

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349236
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.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349317
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))

Open 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...
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349360
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.
0
 

Author Comment

by:willie108
ID: 38349418
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))

Open in new window

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

Open 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")

Open 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

Open in new window

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

Assisted Solution

by:krishnakrkc
krishnakrkc earned 1332 total points
ID: 38349461
Hi

Add these lines before the End If

With Worksheets("mbPerFileFromPerl").Range("a2").Resize(i)
    .TextToColumns Destination:=.Cells(1), DataType:=1, FieldInfo:=Array(1, 3)
End With

Open in new window


Kris
0
 

Author Comment

by:willie108
ID: 38349476
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

Open 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

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349570
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.
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38349608
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

Open in new window


Kris
0
 

Author Comment

by:willie108
ID: 38349822
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.
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38349880
Hi

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

.NumberFormat = "mmm-dd-yyyy"

Open in new window


Kris
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38349918
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
0
 

Author Comment

by:willie108
ID: 38349953
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
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38350582
Hi

Reaplce

Array(1,3)

with

Array(1,4)

Kris
0
 

Author Comment

by:willie108
ID: 38352925
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?
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 1332 total points
ID: 38353045
replace

.NumberFormat = "mmm-dd-yyyy"

with

.NumberFormat = "mm/dd/yyyy"

Kris
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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

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

Join & Ask a Question