1. YRMO
Write another VBA function called =YRMO that will turn a date into the year and month, such that 12/8/14 would return: 2014.12
This is useful for tagging dates so that you can aggregate information in a long list into monthly buckets.
2. Read
Do the following readings:
1. From the VLOOKUP Book, please read the pages 1-24.
2. From the course reader: please read Benjamin Graham’s Intelligent Investor, Chapter 16: “Margin of Safety” as the Central Concept of Investment.
3. Watch
You may want to watch the following videos:
This video on the =LOOKUP function is pretty useful, especially towards the end.
Excel Lookup Series #7: LOOKUP Function
And this video is very long, but provides a lot of examples that go far beyond the basics of =LOOKUP. Watch any parts that you’re interested in.
LOOKUP function Beginner to Advanced 23 Examples (Excel VLOOKUP WEEK Video #2)
4. Lynda
It would be a good idea to spend some time on Lynda.com:
In the course: Excel 2013: Advanced Formulas and Functions with Dennis Taylor, watch the first three videos about LOOKUP functions.
Financial Modeling and Data Analysis 
Regarding the date functions: it might be helpful to read these pages:
http://www.techonthenet.com/excel/formulas/month.php
http://www.techonthenet.com/excel/formulas/year.php
this one says the same basic thing:
http://msdn.microsoft.com/en-us/library/88k2aec8%28v=vs.90%29.aspx
LikeLike
A student asked a question:
I am also a bit stuck with the code trying to generalize it for any data. I think computer does not understand mm/dd/yyyy. Here is my code that only works for 1 data. Could you give me a hint on how to generalize it?
Function YrMo(Text As String)
Dim thisDate As Date
Dim LYear As Integer
Dim LMonth As Integer
thisDate = #12/8/2015#
LYear = Year(thisDate)
LMonth = Month(thisDate)
YrMo = LYear & “.” & LMonth
End Function
My response is:
Excel stores dates as numbers, but you format them as dates. For example, today is January 8, 2015. That date is stored in excel as 42012. So, the input should be a date (which is really a number). It should not be text. A mistake that people often make in excel is to format their dates as text.
So, try using a date as the input:
Function YrMo(inputDate As Date) as String
LikeLike
I got this and can confirm that it works:
Function YRMO(inputDate As Date) As Date
YRMO = Format(inputDate, “yyyy.mm”)
End Function
LikeLiked by 1 person
I like that you did it in a single line.
Here’s what I use:
Public Function yrmo(baseDate As Date) As String
‘============================================
‘ Converts a date of 5/21/2010 into 2010.05 for easy aggregation of data by month
‘============================================
‘yrmo = Year(baseDate) + Month(baseDate) / 100
If baseDate = 0 Then
yrmo = “”
Else
yrmo = Format(Year(baseDate) + Month(baseDate) / 100, “###0.00”)
End If
End Function
LikeLike