Google Docs How To
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.