Convert Date to YYYY.MM Number

[David Schaleger]

I need VBA to convert several dates in a column to a 2-decimal number representing just the year and month in a YYYY.MM in the right adjacent column.

This is to faciliate filtering the date by month(s) in a pivot table. Currently, filtering the "date" field when it's placed in the "report filter" box of a pivot table pales in comparison to filtering the same date field in the data table, where I can actually select an entire month with one mouse click on a check box. Dragging the date field in the "row labels" box means using cumbersome filtering options AND having the date show up in and messy up the report when I don't need it to. So, I use the YYYY.MM format to easily select entire months with one mouse click with the date field in the "report filter" box. But manually converting dates to this format is cumbersome, as well. Thus the need for VBA.

 

[Joe4]

In an Excel formula, it would simply be:
=TEXT(A1,"yyyy.mm")

In VBA, it would look like:
FORMAT(Range("A1"),"yyyy.mm")

 

[VoG]

Try like this

Sub ConvDates()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        .Value = Year(.Offset(, -1).Value) + Month(.Offset(, -1).Value) / 100
    End With
Next i
End Sub

 

[David Schaleger]

In an Excel formula, it would simply be:
=TEXT(A1,"yyyy.mm")

Awesome and SIMPLE! Thank you very much! So many functions, yet so very few concise resources to figure them all out. I guess I'll get them all figured out - one function at a time...

 

[Joe4]

So many functions, yet so very few concise resources to figure them all out. I guess I'll get them all figured out - one function at a time...

Wise man says: "The journey of a thousand miles begins with a single step!"

Actually, the built-in Excel help is not a bad resource for functions. They usually have detailed explanations and examples. The "Insert Function" formula builder in Excel also at least breaks them down by category, so it can help narrow the scope. I usually start there first and check the category I need and see if there is anything that looks like it might help, then check out the built-in help files on that function.

Of course, sometimes you have no idea what to use or how to even approach a problem. That's where we come in!<!-- / message -->

 

[VoG]

This may help you http://www.hoffits.com/