try
vday=day(cdate(mydate))
--
hemu


"Bob Phillips" wrote:

Does this do it?

Sub ReformatDates()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
With Cells(i, "A")
.Value = (Mid(.Value, 4, 3) & Left(.Value, 3) & Mid(.Value, 7,
4))
End With
Next i

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"snowing" wrote in
message ...

It's trying to change the content in the cell.

I have a date in say cell A1 that looks like this, 30/05/2006 9:33:03
AM
I want to split up the first 2 parts of it and store them into
variables.
Do I first have to store the date and time in a variable of it's own in
order to store the 30 and the 05 in there own variables ?
What i want to do is create a macro that will look at a ranage of cell
which contain a date and time like 30/05/2006 9:33:03 AM and split the
first 2 numbers up ( the 30 and the 05 ) store them in varables and
then reverse then within the cell so the date reads 05/30/2006 9:33:03
AM.

Why do I want to do it this way you ask.

The date is taking from access, and is pulled from a long number
generated by another program.
I can not change the date to format in access due to the other program
and I can not get the date to format in Excel because of this, I need
that columns date to be formatted as mm/dd/yyyy hh:mm:ss AM/PM.
And no, Regional option in control panel will only mess up access
dates.


--
snowing
------------------------------------------------------------------------
snowing's Profile:
http://www.excelforum.com/member.php...o&userid=34576
View this thread: http://www.excelforum.com/showthread...hreadid=558237