Hello,

Does anyone know how I could take a date like this..

30/05/2006 9:33:03 AM

split it up and store the first part of it in variables. So 30 would be
stored in a variable of its own, 05 would be stored in a variable of its
own and 2006 would be stored in a variable of its own.

Thanks


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

vDay = Day(the_date_variable)

vMonth = Month(the_date_variable)

vYear = Year(the_date_variable)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"snowing" wrote in
message ...

Hello,

Does anyone know how I could take a date like this..

30/05/2006 9:33:03 AM

split it up and store the first part of it in variables. So 30 would be
stored in a variable of its own, 05 would be stored in a variable of its
own and 2006 would be stored in a variable of its own.

Thanks


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

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

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

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

Thanks Bob.

I'm getting a Syntax error...
Value = (Mid(.Value, 4, 3) & Left(.Value, 3) & Mid(.Value, 7,
4))


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