Google Docs How To

Google Spreadsheet

How to sum values falling between two dates


How to show day for monday of Nth week

  • B2 is the start date
  • S1 is the number of the week from the start date

How to calculate a scale from 0 to 1 based on a number of working days in a week, that fall between a start date and an end date

  • E2 is the range start
  • T2 contains monday of the next week's range
  • Financials!... range is the list of holidays
  • F2 is the range end
  • S2 is monday of this week


How to get heading for a cell that shows under it, filtering by start and end dates

The heading is identified by "Lead:" in column C. Look up by A's cell content.
Where AK contains the following
Where A2 is the lookup criteria, H2 contains the auto calculated project start date:
Projects tab F and E are the lookup criteria start and end dates respectively

How to calculate range based upon some criteria

  • We want the sum of a range. OFFSET provides a range, based upon a starting point (optional offset in x and y) and then some dimensions.
  • A1 is the reference corner, 0 and 0 are the offsets (we want to stay in the A column). the 1 at the end means 'only the A column'.
  • the 'height' parameter of the OFFSET function is calculated by row of empy cell - ROW(A1) (which is the starting cell)
  • INDEX(ROW(A1:A), MATCH(TRUE, ISBLANK(A1:A), 0)) calculates the rownumber of the first empty cell below A1.


@TechnicalNotes @Google