Convert the date to day of the week.

[Nils Titley]

Ok,

I see that there is a TextToColumns. Can you help me. Can I read the file
and write it back out over it self or do I have to create a new file?

If you're dates in the CSV file are in dmy order, then I would rename the
workbook to *.txt. Then I could import the data and specify that field as a
date in dmy order.

I _think_ that what's happening is that other users have a different data
order. The data that could pass for dates in that formatted will be converted
to a real date -- but probably not what you want.

For instance if the data shows: 12/02/2008
and you bring it in with your shortdate format of dmy, then you'll see a date of
Feb 12, 2008.

But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2,
2008.

The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't
tell if the date is really want the CSV intended.

And those "dates" that do not look like dates that match the users short date
format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25,
2008.

For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a
date.

So my recommendation is to not rely on the user's setting. Bring the data in a
different way--either by renaming the file as *.txt (so you see the wizard).

You could also try: Data|Import external data|get data to see the same text to
columns wizard.

====
My real point is just because the value looks like a date doesn't mean that it
is a date. And even if it is a date, it may not be what the original data
intended.

 

[Nils Titley]

Ok but isn't there a built in function that will do the conversion without
calling another macro?

I am feeling a lot out of my element here. I have run into these problems
before but that is when I was programming full time. I am just learning how
to do what I want in VB with Excel.

Short example?

 

[Dave Peterson]

The builtin function is data|text to columns. You could use worksheet functions
and parse the string into its parts, but it would require extra cells and would
be slower.

=date(right(a1,4),mid(a1,3,2),left(a1,2))

I recorded a macro when I selected a column and did the data|text to columns.

Then I tweaked the code:

Option Explicit
Sub testme()

Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("D1").EntireColumn
End With

With myRng
.TextToColumns Destination:=.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 4)

.NumberFormat = "mmmm dd, yyyy"
.AutoFit
End With

End Sub

Ok but isn't there a built in function that will do the conversion without
calling another macro?

I am feeling a lot out of my element here. I have run into these problems
before but that is when I was programming full time. I am just learning how
to do what I want in VB with Excel.

Short example?

 

[Dave Peterson]

Ps.

I still don't know how you're opening the 60 .csv files.

But Ron de Bruin has some options here:
http://www.rondebruin.nl/csv.htm
http://www.rondebruin.nl/txtcsv.htm

Ok but isn't there a built in function that will do the conversion without
calling another macro?

I am feeling a lot out of my element here. I have run into these problems
before but that is when I was programming full time. I am just learning how
to do what I want in VB with Excel.

Short example?

 

[Ron Rosenfeld]

Ron,

I am not sure this is helping....

I changed what you have because I don't have #2/12/2008# I have "02/12/2008".

If you did that, in the CONST line, you would have gotten a "type mismatch"
error. So I don't understand what you mean here or what you did.

When I do that I get debug print
A) 02/12/2008 = 39490 120208 (ddmmyy)
B) 12/02/2008 = 39784 021208 (ddmmyy)

A) ActiveCell.Offset(0, 17).Value = Format(CLng(dt), "ddmmyy")
B) ActiveCell.Offset(0, 18).Value = Format(CLng(dt2), "ddmmyy")
Results
A) 120208
B) 21208

I tried, what I call casting, RunDate(MyNum) as
CLng(RunDate(MyNum))

I'm not familiar with your term "casting" in this context. So again, I don't
understand what you mean.

ActiveCell.Offset(0, 0).Value = Format(CLng(RunDate(MyNum)), "ddmmyy")
Data = 12/02/2008
Result = 21208
Expecte as above 120208

Please, I don't get it and don't know what to do.

Thanks

Let me repeat: "Excel stores dates as serial numbers"

If you have a value, expressed as a serial number, of 21208 (or 021208), and
then try to express that as a date, it will translate to be 21,208 days after 1
Jan 1900, or Thursday, January 23, 1958.

You still haven't indicated what your RunDate function is doing, or what MyNum
is.

Also, perhaps you do not understand the formatting a cell as a date has
AABSOLUTELY NOTHING TO DO with how Excel interprets the value in that cell. It
only determines how the value in that cell is displayed.

Excel interprets date input according to the settings/format in the REGIONAL
AND LANGUAGE settings (in Control Panel). Usually, that format defines the
order (dmy, mdy, etc) as well as the separator (usually a "/").

If your data input does not conform to these specifications (i.e. if your data
is 120208 instead of 12/02/08), you will need to manipulate the data in order
to correct this.

If you could answer the questions I've posed, it should be possible to help.
--ron

 

[Nils Titley]

Dave

I open them one of the time after I have gotten all the files names. I read
a file, process that file print the data to a worksheet.. continue

' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath <> ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
If (FilesInPath <> "") Then
NumFiles = NumFiles + 1
End If
Loop

Set WBNew = Workbooks.Add

' Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)

Set FileBook = Workbooks.Open(MyPath & MyFiles(NumDone))


Does that help.

 

[Nils Titley]

In C you change the type to another type - my be my memory is wrong but I
believe the term is called casting

 

[Dave Peterson]

So you're opening the .CSV files via a macro.

You can add the code to convert the numbers to dates right after you open the
..CSV file.

Dave

I open them one of the time after I have gotten all the files names. I read
a file, process that file print the data to a worksheet.. continue

' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath <> ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
If (FilesInPath <> "") Then
NumFiles = NumFiles + 1
End If
Loop

Set WBNew = Workbooks.Add

' Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)

Set FileBook = Workbooks.Open(MyPath & MyFiles(NumDone))

Does that help.

 

[Nils Titley]

Dave,

I have been thinking that it might help if I tell you more about the process.

Each file will contain 1200 to 2000 rows of data. 10% of the data will not
be used. The data lists the date of pickup, the truck that made the pickup,
the lat and long and some other informtion. There will be in most cases
three runs per file. As I parse through each row in the worksheet, I am
totaling # of pickups, meters between pickups and the total number of bins
picked up and other data. After I parse all the rows, I generate three rows
to the report worksheet - one row for each run. So in reality, I only have
to convert three dates from the numerical format to a date format.

I have not tried the routine you provided earlier as of yet. But I have an
additional question. Since the date is a numerical value, could I store that
value in a Long veriable and convert to the date just before I print it since
that is the only time I need it in that form and it is the only piece of data
that is giving me a hard time. It seems like it would save processing and 3
out of 2000.

What do you think?

Thanks

 

[Dave Peterson]

You can use code like this to change 25022008 to a date:

Dim myCell As Range
Dim myDate As Date

Set myCell = Somerangehere

With myCell
myDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2))
.NumberFormat = "mmmm dd, yyyy"
.Value = myDate
End With

 

[Nils Titley]

Dave

Thanks for the code. I will give it a try. I am doing some more analysis
of the data. I may be running into user changing the data and so it is not
in its raw form. When it is raw the date value is not in a date form but in
a general form.

I am going to add your code to see what happens.

I leave another message when I know some thing.

Dave, I appreciate the help you are giving me and the other suggestions you
have made. You have been a big help to me.

 

[ward376]

Nils - with the regional date settings you've described, and the
imported "date" in cell a2, in a cell formatted "general" the formula
below will return the NUMBER that represents the date for the ddmmyyyy
format you've described. Your example of 25022008 would appear as
39503 representing February 25, 2008.

=VALUE(CONCATENATE(MID(a2,3,2),"/",LEFT(a2,2),"/",RIGHT(a2,2)))

Then you can FORMAT it as a date.

Cliff Edwards

 

[Nils Titley]

Dave

I did this:

Dim testDate As Date
With ActiveCell.Offset(0, 0)

testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value,
2))
.NumberFormat = "dd/mm/yy"
.Value = testDate
End With

But I got a run time 13 error on the testDate line.

What am I actually doing with Right (.Value, 4) is that for the year,
Mid (.value, 3, 2) is that for the day and Left .... for the month.

Why did it stop. Can't I access the cell the way I did it?

Thanks

Dave

Thanks for the code. I will give it a try. I am doing some more analysis
of the data. I may be running into user changing the data and so it is not
in its raw form. When it is raw the date value is not in a date form but in
a general form.

I am going to add your code to see what happens.

I leave another message when I know some thing.

Dave, I appreciate the help you are giving me and the other suggestions you
have made. You have been a big help to me.

 

[Dave Peterson]

I'm guessing that the value in the Activecell isn't what you expected.

Add a line:

msgbox Activecell.value

before the line that causes the error.

ps.

Although this works:
Activecell.offset(0,0)
the .offset(0,0) doesn't really do much.

Why not just use Activecell?

 

[Nils Titley]

I am going to add more. It had a problem because it doesn't have a value to
work on. I am having a little trouble seeing how this works.

For example
RunDate(Beat) = ActiveCell.Offset(0, 1).Value This takes the value and
loads the variable.
ActiveCell.Offset(0, 0).Value = Format((RunDate(MyNum)), "dd/mm/yy")
This line loads the date into the cell I want to store the date.

At what point do I use your new code and I don't see how to load the
variable or the value. Please exaplain more...

Thanks sorry I don't see it.

 

[Dave Peterson]

I don't understand what rundate does. It looks like it just holds a number and
doesn't do anything to convert that string of digits into a real date.

dim myDate as date
with activecell
if isempty(.value) then
'what happens if it's empty???
else
myDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2))
.numberformat = "mmmm dd, yyyy"
.value = mydate
end if
end with

 

[Nils Titley]

RunDate(Beat) = ActiveCell.Offset(0, 1).Value

Dim testDate As Date
With ActiveCell.Offset(0, 1)
MsgBox ActiveCell.Value
testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2),
Left(.Value, 2))
.NumberFormat = "dd/mm/yy"
.Value = testDate
End With

Msgbox showed : 1 how can that be should the value be a date?

Thanks

 

[Nils Titley]

David,

I made an interesting discovery about the data. When I look at the format
of the date in the CVS file it is in General format as a date. If I try
putting a date in it is in date format. If I change a date to changes it to
the date number.

How do I format the whole column in the general format?

I still want to change the numbers but I can't get what you provided to work.

RunDate(Beat) = ActiveCell.Offset(0, 1).Value

Dim testDate As Date
With ActiveCell.Offset(0, 1)
MsgBox ActiveCell.Value
testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2),
Left(.Value, 2))
.NumberFormat = "dd/mm/yy"
.Value = testDate
End With

Msgbox showed : 1 how can that be should the value be a date?

Thanks

 

[Dave Peterson]

If you run it more than one time, the original value was replaced with a date.

RunDate(Beat) = ActiveCell.Offset(0, 1).Value

Dim testDate As Date
With ActiveCell.Offset(0, 1)
MsgBox ActiveCell.Value
testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2),
Left(.Value, 2))
.NumberFormat = "dd/mm/yy"
.Value = testDate
End With

Msgbox showed : 1 how can that be should the value be a date?

Thanks

 

[Dave Peterson]

Try recording a macro when you select a column and change the format.

I don't have another guess why you're having trouble. You'll have to provide
more details of what you tried.