# [HW 1.3c] VBA Date Functions and Readings

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

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

Like

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

Like

3. I got this and can confirm that it works:

Function YRMO(inputDate As Date) As Date
YRMO = Format(inputDate, “yyyy.mm”)
End Function

Liked by 1 person

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

Like