Macro Date Manipulation

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

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.

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
>
>

I used this code

Please Login or Register  to view this content.

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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?

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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.

Nevermind, I've got it

Please Login or Register  to view this content.

Thanks for your help! Its greatly appreciated Tom.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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.