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 wrote:

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

"Nils Titley" wrote:

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" wrote:

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 wrote:

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" wrote:

Where did you get MyNum?



Nils Titley wrote:

Ron,

I confess, I don't know how to get the numeric value of it?

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

Thanks to all for responding but I am wondering. Will I have a problem with
12/02/08. This is not December 02,08 but it is Feb 12, 08. The dd/mm/yy has
already caused a problem. The macro is being used in South Africa. I had to
force format on a date because it was changing the format.

The code below produced 02/12/08 when it is 12/02/08. It worked only after
I changed below to Format(RunDate(MyNum), "dd/mm/yy")

With ActiveCell.Offset(0, 0)
.NumberFormat = "dd/mm/yy"
.Value = RunDate(MyNum)
.HorizontalAlignment = xlCenter

How do I force the day of the week?

Thanks , I hope I am making sense.

I believe you may be misunderstanding how dates are being entered into Excel.

Excel stores dates as serial numbers with 1 = 1 Jan 1900. It then formats that
result depending on the cell format.

However, a date expressed like 12/02/08 is ambiguous. So when you enter that
value into a cell, Excel has to decide if you mean 12 Feb or 2 Dec. It does
this by looking at the date settings -- NOT in Excel -- but at Control
Panel/Regional and Language Options.

So if your code is producing 2 Dec when you expect it to be producing 12 Feb,
you need to look at the output of RunDate(MyNum). If the numeric value is
39490, then it is returning 12 Feb; if 39784, then 2 Dec.

If it is returning the desired date, then
.numberformat = "dddd" will return the correct day of the week.
--ron


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson