In cell D3 I owuld put the following formula:
=if(EOMONTH($A1+70,0)=EOMONTH(D2,0),"Output","No report due")
I assumed the $A1 to make sure that it works for all columns.
The formula just compares the end of the month in the second row with the end of the month for the date in cell a1 + 70 days.
If the date is 1/1/2017 then 70 days later is 3/12/2017, so the end of that month is 3/31/2017. So the phrase Output appears when the current month has an end date equal to 3/31/2017.
It will work for all columns as long as they have a date in the 2nd row.
Wilson. Here is another approach (assuming the date is in A1:C):
=ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),QUERY(A:C,"Select * Order By B Desc",1),{1, 2, 3},FALSE)))
Just be sure to format the resulting date column as dates, since by default is will use the raw number format of the data (i.e., the number of days since December 30, 1899, which will be numbers in the mid-40,000 range).
HOW IT WORKS:
Essentially, we form a virtual range of just the UNIQUE donors (as well as the header) with UNIQUE(A:A). (This will also include one blank, since that would be unique in the column one time.)
We VLOOKUP only those values in another virtual range: a QUERY of the A:C data upside-down by date (i.e., with the most recent dates always on top). By looking up only unique donors and the header from A in this upside-down range, the first match found will always be their most recent donation.
We return columns 1, 2 and 3 from this upside-down QUERY range (i.e., the three points of data, and the headers).
FALSE tells Google Sheets that the virtual range is not in order by the key column (Donor) and instructs it therefore to look for exact matches only.
The IFERROR wrapping on the VLOOKUP prevents the formula from trying to return a result for that UNIQUE blank I mentioned.
Best Answer
The query would be like this:
(or >= if you want to include 3 PM sharp)
Here, the single-quoted string 15:00:00 is preceded by the keyword
timeofday
, indicating its meaning. This is similar to how date strings are preceded bydate
. Note that the format of query string is fairly rigid: it has to be 24-hour time, with the seconds field included (HH:mm:ss). However, the time data in your spreadsheet can be in whatever format you want.See scalar functions for more examples of manipulation with timeofday values.