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

Advertisements

4 comments

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: