Saturday, March 24, 2012

Excel Quick Tip: Finding Year and Quarter from dates

I deal with huge data around dates all the time.  Be it daily revenue, volume or activities. Though daily data is interesting,  we often need to aggregate data into weeks, months or quarters, to see trends, forecast projections and track them against targets.

Here is a quick tip on getting you the year, month, quarter and weeks from a given date. Read on.

The formula is quite simple.
Year= Year(A2) 
Month= Month(A2) 
WeekOfYear= WEEKNUM(A2)

Formula for Quarter and Sem Annual labels are bit tricky.
Quarter = "Q"&INT((MONTH(A2)-1)/3+1)
Month ="H"&INT((MONTH(A5)-1)/6+1) 

Simple. Isn't it. Here is another tip. When you press Control + ~ (tilde), all the formulas on your sheets will be visible. Press the same combination to turn this off.  Happy Excel Crunching!

No comments: