Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Phone Manipulation | Excel Discussion (Misc queries) | |||
Date Manipulation | Excel Discussion (Misc queries) | |||
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation | Excel Programming | |||
Problem with Date Manipulation | Excel Discussion (Misc queries) | |||
Date manipulation question | Excel Programming |