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

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