Extract a YearMonth value from a Date
 

I want to populate a Month Column (with the format YMM) from the
adjacent Date column,
through a loop which would translate a date like 8/15/2007 to 708.
The Excel formula is Year(DateField)*100+Month(Datefield)-200000
Now , I want to incorporate this logic in my loop code, and for good
measure, add an IsDate test.
I am still a newbie. I tried to get the help of the Macro Recorder,
but I am stuck.
Can you help me with this code ?. Thank you.

Dim RowNdx As Long: Dim LastRow As Long: LastRow =
ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 2 Step -1
If IsDate(Cells(RowNdx, "Z").Value) Then
Cells(RowNdx, "Y").Value = "=YEAR((Cells(RowNdx,
'Z').Value)*100+MONTH((Cells (RowNdx, 'Z').Value)-200000"
Next RowNdx

Celeste

Extract a YearMonth value from a Date
 

One way:

Dim rCell As Range
For Each rCell In Range("Z2:Z" & _
Range("Z" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then _
.Offset(0, -1).Value = Right(Format(.Value, "YYMM"), 3)
End With
Next rCell

In article . com,
u473 wrote:

I want to populate a Month Column (with the format YMM) from the
adjacent Date column,
through a loop which would translate a date like 8/15/2007 to 708.
The Excel formula is Year(DateField)*100+Month(Datefield)-200000
Now , I want to incorporate this logic in my loop code, and for good
measure, add an IsDate test.
I am still a newbie. I tried to get the help of the Macro Recorder,
but I am stuck.
Can you help me with this code ?. Thank you.

Dim RowNdx As Long: Dim LastRow As Long: LastRow =
ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 2 Step -1
If IsDate(Cells(RowNdx, "Z").Value) Then
Cells(RowNdx, "Y").Value = "=YEAR((Cells(RowNdx,
'Z').Value)*100+MONTH((Cells (RowNdx, 'Z').Value)-200000"
Next RowNdx

Celeste

Extract a YearMonth value from a Date
 

Woww !!!. I do appreciate your solution.
I want to extend this code further, using other postings,
but I get Errors in trying to sum Columns.
I am also trying to copy Entire Row for Invalid Date on separate
Worksheet
Help appreciated.

Dim rCell As Range
Dim myRow As Long
For Each rCell In Range("Z2:Z" & _
Range("Z" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then _
.Offset(0, -1).Value = Right(Format(.Value, "YYMM"),
3)
' Sum 5,6,... Columns to 16 Column . Error on next
statement
.Offset(0, 16).Value = Offset(0, 5).Value + Offset(0,
6).Value
'If Date is greater than Today, Copy Entire row to
Invalid Dates worksheet
'If ActiveCell.Value Now()
' myRow = Sheets("InvalidDates").Cells(Rows.Count,
3).End(xlUp)(2).Row
' Intersect(Target.EntireRow,
ActiveSheet.UsedRange).Copy

Else
.Entire.row.Delete
End If

End With
Next rCell