[HW 1.4c] Quarters

Quarters
You’ve learned how Excel stores dates as numbers, and how, because of that, you can format a date in Excel with codes.  There are various codes for months, days, and years.  There are not, however, codes for quarters.  So you if want to turn 10/26/15 into 4, because that date is in the fourth quarter, you’ll have to be a little creative.

  1. Come up with an excel function using =LOOKUP that will return quarters.
  2. Develop a visual basic function that takes a date and returns the quarter.
  3. Write a VBA function called =QY that returns the quarter and year, such that 1/8/15 would return: Q1 ‘15

 

Advertisements

3 comments

  1. When using LOOKUP function do we have to make 3 columns (range of dates and Quarter) to “tell” Excel which date belongs to which quarter? Or is it simpler?
    Thank you.

    Like

  2. There are several ways to do this with a LOOKUP function. I’ll leave it up to you to figure out the various ways to approach it.

    Regarding the VBA portion, look at the last slide from today for how to calculate quarters.

    Like

  3. Sarah Breckinridge · · Reply

    1.
    Excel Equation: =Lookup(Month(enter date here), Range)

    2.
    Function Quarter(date1 As Date) As Integer

    Quarter = Round(Month(date1) /3,0)

    End Function

    3.
    Function QY(date2 As Date) As String

    Dim date 3 = Format(date2,”mm/dd/yy”)

    Dim quart As Integer
    Dim yr As Integer

    quart=Quarter(date3)
    yr=Year(date3)

    Qy = “Q” + quart = ” ” + yr

    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: