This is the simplest way I know of to find calendar days in a month, using the “zeroth” day of the following month. Formula makes a DAY from supplied inputs:

1) year 2) month + 1 3) zero.

The formula looks like this:

=DAY(DATE(YEAR, MONTH+1,0))

Pretty simple! Another small trick is to use ROW() to get a simple incrementing counter. ROW() returns the current row, and you can add or subtract a base number from it to use as your counter for 1 to 12 (for the month in this example).

The months begin on Row 3, but only 1 is subtracted because we need to arrive at month incremented by one. So, in A3 (Jan), we are finding days in month using day 0 of February.

=DAY(DATE($B$2,ROW()-1,0))

Days in Month

Let’s look at 2016 (a Leap Year) to check that February always works…

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS...

Humans seem to love the symmetry of circles, and the business world has adopted this pleasing form in the shape of the Pie Chart. KRS now challenges our clients to consider the graphs we use, as the data representation must be very closely aligned with the information intended to be conveyed.

Statistics may lie, and they lie most consistently through misleading data representations as you will read below.

Stephen Few has written at length in the past about the failures of pie charts and circular dashboard gauges, and his comments are a good illustration of what goes wrong when tools encourage “nice looking” graphs that don’t truly convey the data properly.

From Stephen Few, Perceptual Edge:

“Although the evidence of a pie chart’s dysfunction is hard to ignore when you actually take a moment to inspect it, nothing that I teach is met with such fierce opposition as my low opinion of...

Here’s a small selection of useful functions, including CELL, MID and FIND.

Typing “=CELL(“ in Excel will drop this menu down.

Most are self-explanatory, but there are some uses for CELL with filename that are “different”. For example, you can extract the sheet name from filename by using the MID function and looking for the square brackets after the filename.

First try =CELL(“filename”), to see what result you get.

To extract just the filename, with no path, try:

=MID(CELL(“filename”),1,FIND(“
[“,CELL(“filename”))-1

To extract the current sheet name:

=MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255)

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS can assist you with your Excel: enquiries@krs.co.dev

Here is our example spreadsheet:

The SUM formula in cell B15 is shown. It uses MONTH(NOW()), but could just as easily use a Named Formula:

=SUM(OFFSET(Totals2009,0,0,ThisMonth,1))

Here’s the formula in Name Manager:

How it works

1. OFFSET’s 4th parameter is Height.
2. Pass it current month against named range “Totals2009”, i.e. cells B3:B14 (or Named Formula ThisMonth)
3. Here, it sums 10 rows against MONTH(NOW()), (October)

This MTD example, on 26th of any month, compares September & October:

Conditional formatting to highlight rows is based on the formula:

=(ROW()-ROW(I$2)=$H$24)

Where $H$24 uses COUNTIF in place of OFFSET:

=COUNTIF(OctDates,”<=”&(DATE(YEAR(H3),MONTH(H3),DAY(NOW()))))

OctDates is a range of all the days in October colums , formatted to show “d” (day) only, starting at row H3. September range is treated similarly, but green background cells are Saturdays...