Macro Date Manipulation
 

Does anyone know how to manipulate a date from a mmdd format to
mm/dd/yyyy format? My main concern is how would I add a year t
something that doesn't have it already. Any help is appreciated

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

Just to add some more explanation. I get an excel file every week i
which I have a column (column I) and the date is in this format, s
eventually I'm going to have to figure out a way to loop this up th
column through the rows to change the date formats

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

The following line of code will change the format of the active cell,
assuming that you have an actual date (and not text on number) in the cell.

activecell.numberformat = "mm/dd/yyyy"

This line will do an entire column...
columns("A").numberformat = "mm/dd/yyyy"
--
HTH...

Jim Thomlinson


"DKY" wrote:


Just to add some more explanation. I get an excel file every week in
which I have a column (column I) and the date is in this format, so
eventually I'm going to have to figure out a way to loop this up the
column through the rows to change the date formats.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

I used this code


Code
-------------------
Public Sub COLUMN_VALUES()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Const shtName As String = "V_s" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " V_s sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set rng1 = Intersect(.UsedRange, .Columns("I"))
End With

Set rng1 = rng1.Offset(1).Resize(rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

XIT:
Application.ScreenUpdating = True
Range("A1").Select

End Sub
'<<===============================
'===============================
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<==============================
-------------------


and it changes this
0224
to this
Aug-11-00
and when I click on the cell, it looks like this
8/11/199

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

select one of the cells and look in the formula bar. What does it look
like.

mmdd
or
mm/dd/yyyy

if mmdd then

Sub Convert_to_Date()
for each cell in selection
cell.Value = DateValue(left(cell,2) & "/" & mid(cell,3,2) & _
"/2006")
cell.Numberformat = "mm/dd/yyyy"
Next
End Sub.

If it looks like mm/dd/yyyy then just select the column and do
Format=Cells=Numberformat and select one of the date formats.

--
Regards,
Tom Ogilvy

"DKY" wrote in message
...

Just to add some more explanation. I get an excel file every week in
which I have a column (column I) and the date is in this format, so
eventually I'm going to have to figure out a way to loop this up the
column through the rows to change the date formats.


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

If that works, then all you had to do was select the column and do
Format=Cells. Probably fewer keystrokes than running the macro. And you
could have formatted the column as mm/dd/yyyy like you said you wanted.

--
Regards,
Tom Ogilvy


"DKY" wrote in message
...

I used this code


Code:
--------------------
Public Sub COLUMN_VALUES()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Const shtName As String = "V_s" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " V_s sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set rng1 = Intersect(.UsedRange, .Columns("I"))
End With

Set rng1 = rng1.Offset(1).Resize(rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

XIT:
Application.ScreenUpdating = True
Range("A1").Select

End Sub
'<<===============================
'===============================
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<===============================
--------------------


and it changes this
0224
to this
Aug-11-00
and when I click on the cell, it looks like this
8/11/1990


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

When I click in the cell, I get this
0224
and that code you posted works great! One more problem, what if I hav
something like this?
1120
It should be 2005 but with your code, it will make it 2006, right

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

What you have there is not a date. It is just a 4 digit number. You can use
Tom's code to make it into a date... His code requires a selection, but you
can just change it to rngI and use what he has.
--
HTH...

Jim Thomlinson


"DKY" wrote:


I used this code


Code:
--------------------
Public Sub COLUMN_VALUES()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Const shtName As String = "V_s" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " V_s sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set rng1 = Intersect(.UsedRange, .Columns("I"))
End With

Set rng1 = rng1.Offset(1).Resize(rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

XIT:
Application.ScreenUpdating = True
Range("A1").Select

End Sub
'<<===============================
'===============================
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<===============================
--------------------


and it changes this
0224
to this
Aug-11-00
and when I click on the cell, it looks like this
8/11/1990


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

Yes it will, but if you have some rule to apply, it can be adjusted.

What months will be 2005 and the rest 2006?

Is it based on the current month?



Let's assume that months 01 and 02 are in 2006

Sub Convert_to_Date()
for each cell in selection
if clng(Left(cell,2)) 2 then
cell.Value = DateValue(left(cell,2) & "/" & mid(cell,3,2) & _
"/2005")
else
cell.Value = DateValue(left(cell,2) & "/" & mid(cell,3,2) & _
"/2006")
end if
cell.Numberformat = "mm/dd/yyyy"
Next
End Sub

--
Regards,
Tom Ogilvy


"DKY" wrote in message
...

When I click in the cell, I get this
0224
and that code you posted works great! One more problem, what if I have
something like this?
1120
It should be 2005 but with your code, it will make it 2006, right?


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

Tom Ogilvy Wrote:
If that works, then all you had to do was select the column and do
Format=Cells. Probably fewer keystrokes than running the macro. An
you
could have formatted the column as mm/dd/yyyy like you said yo
wanted.

--
Regards,
Tom Ogilvy


Excel wouldn't let me format a 4 digit number into a date that look
like mm/dd/yyyy

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

Hey, that looks really good. I like that so far but yes as you said, it
is dependant on today's date. So, say this were May, I would want
anything that's June or later to be 2005.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

Nevermind, I've got it

Code
-------------------
Option Explicit

Sub Convert_to_Date()
Dim Lrow As Long
Dim i As Long
Dim ThisMonth As String
Dim ThisYear As String
Dim LastYear As String

Lrow = Cells(Rows.Count, "A").End(xlUp).Row

ThisMonth = FORMAT(Date, "mm")
ThisYear = FORMAT(Date, "yyyy")
LastYear = (FORMAT(Date, "yyyy") - 1)

For i = Lrow To 2 Step -1
If CLng(Left(Range("I" & i), 2)) ThisMonth Then
Range("I" & i).Value = DateValue(Left(Range("I" & i), 2) & "/" & Mid(Range("I" & i), 3, 2) & _
"/" & LastYear)
Else
Range("I" & i).Value = DateValue(Left(Range("I" & i), 2) & "/" & Mid(Range("I" & i), 3, 2) & _
"/" & ThisYear)
End If
Range("I" & i).NumberFormat = "mm/dd/yyyy"
Next
End Su
-------------------


Thanks for your help! Its greatly appreciated Tom

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=51692

Macro Date Manipulation
 

You posted the code giving me the implication that it worked. I NOW see way
way down at the bottom you indicate that it didn't (something I didn't see
on before I responded) - sorry, incorrect understanding of the result on my
part - nonetheless, I provided code that does work.

--
Regards,
Tom Ogilvy




"DKY" wrote in message
...

Tom Ogilvy Wrote:
If that works, then all you had to do was select the column and do
Format=Cells. Probably fewer keystrokes than running the macro. And
you
could have formatted the column as mm/dd/yyyy like you said you
wanted.

--
Regards,
Tom Ogilvy


Excel wouldn't let me format a 4 digit number into a date that looks
like mm/dd/yyyy.


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924

Macro Date Manipulation
 

Ahh yes, the code you provided worked fine, I then was able to take what
you gave me and make it fit my exact needs. Thanks for your help again.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=516924