Skip to content

Microsoft Excel: Geeks Unite

October 27, 2009

I keep a collection of handy formulas, scripts, and chart styles for use in Excel in my personal.xlsb file. This way, I can easily grab and reuse them for future projects. One thing I often do in Excel is use it as a reporting tool. I usually build three sheets – Data, Formula, and Display. The Data sheet holds all of the raw data, usually either pasted in from other spreadsheets or pulled from other sources using an ODBC connection. The Formula sheet is just that – a collection of formulas that scan the Data sheet and manipulate the information based on parameters I’ve put into the formula, or referenced in other cells. The final sheet, Display, is populated by its own formulas, VBA scripts, or directly from the Formula sheet. This is a straightforward way to auto-build repeatable reports with minor filter applications like Salesperson Name, Region, or Date.

The most common Date filtering can be done using the collection of formulas below. Cut and Paste as needed:

First Day of This Month: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
Last Day of This Month:  =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

First Day of Last Month: =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)
Last Day of Last Month:  =DATE(YEAR(TODAY()),MONTH(TODAY()),0)

First Day of Next Month: =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
Last Day of Next Month:  =DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)

 

Most Recent Sunday:  =IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()-WEEKDAY(TODAY()-1))

Most Recent Monday:  =IF(WEEKDAY(TODAY())-2>0,TODAY()-WEEKDAY((TODAY()-2)),(IF(WEEKDAY(TODAY())-2<0,(TODAY()-5)-WEEKDAY(TODAY()),TODAY())))

Most Recent Tuesday:  =IF(WEEKDAY(TODAY())-3>0,TODAY()-WEEKDAY((TODAY()-3)),(IF(WEEKDAY(TODAY())-3<0,(TODAY()-4)-WEEKDAY(TODAY()),TODAY())))

Most Recent Wednesday:  =IF(WEEKDAY(TODAY())-4>0,TODAY()-WEEKDAY((TODAY()-4)),(IF(WEEKDAY(TODAY())-4<0,(TODAY()-3)-WEEKDAY(TODAY()),TODAY())))

Most Recent Thursday:  =IF(WEEKDAY(TODAY())-5>0,TODAY()-WEEKDAY((TODAY()-5)),(IF(WEEKDAY(TODAY())-5<0,(TODAY()-2)-WEEKDAY(TODAY()),TODAY())))

Most Recent Friday:  =IF(WEEKDAY(TODAY())-6>0,TODAY()-WEEKDAY((TODAY()-6)),(IF(WEEKDAY(TODAY())-6<0,(TODAY()-1)-WEEKDAY(TODAY()),TODAY())))

Most Recent Saturday:  =IF(WEEKDAY(TODAY())=7,TODAY(),TODAY()-WEEKDAY(TODAY()))

Yes, there are times when scripting is better, but formulas can be quick, easy, and very portable. Try these out for your own reports and play around with them to get different results.

Advertisements
No comments yet

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: