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

Wrap-around! One day I will get a foolproof solution for that.

Try this

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 somewhere in email address with gmail if mailing direct)

"snowing" wrote in
message ...

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

Hi Bob,

Wrap-around! One day I will get a foolproof solution for that.

And when you do, I will be first in line to download it!


---
Regards,
Norman

Works Great !!

Thanks,


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

One problem, it's changing all the times to 12:00:00 AM


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

Do you want to keep the existing times, or just to show the date (no time)?

--
HTH

Bob Phillips

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

"snowing" wrote in
message ...

One problem, it's changing all the times to 12:00:00 AM


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

I need to keep the existing times..

I just need to reverse the day and month without affecting the rest o
it.

Thank

--
snowin
-----------------------------------------------------------------------
snowing's Profile: http://www.excelforum.com/member.php...fo&userid=3457
View this thread: http://www.excelforum.com/showthread.php?threadid=55823

Ok, try this

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) & _
Right(.Value, Len(.Value) - 6))
End With
Next i

End Sub


--
HTH

Bob Phillips

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

"snowing" wrote in
message ...

I need to keep the existing times..

I just need to reverse the day and month without affecting the rest of
it.

Thanks


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

Thanks, it does work, but I need it to flip the date from dd/mm/ t
mm/dd

--
snowin
-----------------------------------------------------------------------
snowing's Profile: http://www.excelforum.com/member.php...fo&userid=3457
View this thread: http://www.excelforum.com/showthread.php?threadid=55823

That is what I am doing.

--
HTH

Bob Phillips

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

"snowing" wrote in
message ...

Thanks, it does work, but I need it to flip the date from dd/mm/ to
mm/dd/


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

If the date is *dd/mm/*yyyy hh:mm:ss AM/PM then the formula is not
working for me, it doesn't do anything.
If the date is *mm/dd/*yyyy hh:mm:ss AM/PM it will reverse it to be
*dd/mm/*yyyy hh:mm:ss AM/PM, but the dates that are in the spreadsheet
are formatted as *dd/mm/*yyyy hh:mm:ss AM/PM and I need to change the
date to be *mm/dd/*yyyy hh:mm:ss AM/PM


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

Sorry man, my mistake, working now.
My date in regional options was set wrong, I thing that was causing i
to not do anything.

Is there a way I can get the forumla to skip the first cell, exampl
start with cell A2

--
snowin
-----------------------------------------------------------------------
snowing's Profile: http://www.excelforum.com/member.php...fo&userid=3457
View this thread: http://www.excelforum.com/showthread.php?threadid=55823

Thank goodness for that, I was out of ideas.

Start at row 2

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

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
With Cells(i, "A")
.Value = (Mid(.Value, 4, 3) & Left(.Value, 3) & _
Right(.Value, Len(.Value) - 6))
End With
Next i

End Sub


--
HTH

Bob Phillips

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

"snowing" wrote in
message ...

Sorry man, my mistake, working now.
My date in regional options was set wrong, I thing that was causing it
to not do anything.

Is there a way I can get the forumla to skip the first cell, example
start with cell A2 ?


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

Thanks man

--
snowin
-----------------------------------------------------------------------
snowing's Profile: http://www.excelforum.com/member.php...fo&userid=3457
View this thread: http://www.excelforum.com/showthread.php?threadid=55823