Formatting date


N

Nils Titley

I am in USA. The macro is being used in South Africa. Dates in SA are
written ddmmyy. 12/02/08 is Feb. 12, 08 and not Dec 2, 08. I am having a
problem with the way Excel is formating the date.

This is the code I am using:
The date is 12/02/08 (Feb 02, 08)

1) Example
With ActiveCell.Offset(0, 0)
.NumberFormat = "ddmmyy"
.Value = RunDate(MyNum)
.HorizontalAlignment = xlCenter
End With

1) When you look at the worksheet cell (A19) the date appears as 021208 in
the cell. Click on the cell, at the formula windows it shows 12/2/2008. I
need the cell (A19) to show 120208. Because of this it sorts incorrectly and
places the values in the incorrect location. It sorts based on Dec 2 08.

2) Example
With ActiveCell.Offset(0, 0)
.Value = Format(RunDate(MyNum), "ddmmyy")
.HorizontalAlignment = xlCenter
End With

It now sorts correct. But I think only because the value 21208 is small than
130208.
But when you look at the worksheet cell (A2) the value appears as 21208 in
the cell. Click on the cell, at the formula windows it shows 21208. I need
the cell (A2) to show 120208. And the program needs to think it is Feb 12 08.

The worksheet that the data is printed to is created when the macro runs.

Thanks for your help.
 
Ad

Advertisements

R

Ron Rosenfeld

I am in USA. The macro is being used in South Africa. Dates in SA are
written ddmmyy. 12/02/08 is Feb. 12, 08 and not Dec 2, 08. I am having a
problem with the way Excel is formating the date.

This is the code I am using:
The date is 12/02/08 (Feb 02, 08)

1) Example
With ActiveCell.Offset(0, 0)
.NumberFormat = "ddmmyy"
.Value = RunDate(MyNum)
.HorizontalAlignment = xlCenter
End With

1) When you look at the worksheet cell (A19) the date appears as 021208 in
the cell. Click on the cell, at the formula windows it shows 12/2/2008. I
need the cell (A19) to show 120208. Because of this it sorts incorrectly and
places the values in the incorrect location. It sorts based on Dec 2 08.

2) Example
With ActiveCell.Offset(0, 0)
.Value = Format(RunDate(MyNum), "ddmmyy")
.HorizontalAlignment = xlCenter
End With

It now sorts correct. But I think only because the value 21208 is small than
130208.
But when you look at the worksheet cell (A2) the value appears as 21208 in
the cell. Click on the cell, at the formula windows it shows 21208. I need
the cell (A2) to show 120208. And the program needs to think it is Feb 12 08.

The worksheet that the data is printed to is created when the macro runs.

Thanks for your help.


See discussion in your other thread.
--ron
 
D

Dave Peterson

Where does MyNum come from?

Formatting the cell in an umambiguous date format may make debugging easier:

..numberformat = "mmmm dd, yyyy"

You have at least two active threads for this question. Which one should be
used?
 
Ad

Advertisements

N

Nils Titley

Dave,

I suppose the other one. As you can see I am not having much luck with this
problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top