Google Sheets - Number of days since the last date a range of cells

Question

I know how to return the number of days from a specific date, it requires to input a cell. The thing is, I have an ever-growing list of emails to be sent (in new columns), so the date needs to be updated with the new column.

If 2 columns have 2 dates in, can the formula be something like =minus(TODAY(), between B6 and D6)?

Use the spreadsheet link to test please - https://docs.google.com/spreadsheets/d/1dQNMMiSvfGNSj5mJ4Uu8kbEATPSS35_Cd-wbbfrrGPM/edit?usp=sharing

score 4 · Accepted Answer · answered Mar 21 '19 at 13:59

4

try like this:

=DAYS(TODAY(), D6)

or perhaps like this:

=DAYS(TODAY(), MAX(B6:D6))

answered Mar 21 '19 at 13:59

player0

124,011
12
67
124

Hi player0, I wish to make this slightly more advanced now. I wish to know the days between 1st or last order and next order when name is the same in column A. Here's a link - [link](https://docs.google.com/spreadsheets/d/1_4fWyHkQg7zvLADokZGvV03__guNffV_eaw2Owyfi3w/edit?usp=sharing) Thank you! – Harry Norman May 10 '19 at 14:21
can you pls add your desired output in the C column? – player0 May 10 '19 at 15:51

score 0 · Answer 2 · answered May 11 '19 at 19:36

I wish to make this slightly more advanced now. I wish to know the days between 1st or last order and next order when name is the same in column A

paste in C2 cell and drag down:

=IF(LEN(A2&B2), 
 IF(COUNTIF(INDIRECT("A2:A"&ROW()), INDIRECT("A2:A"&ROW()))>1, 
 DAYS(B2, MAX(QUERY({INDIRECT("A2:B"&ROW()-1)}, 
 "select Col2 where Col1 ='"&A2&"'", 0))), "1st Order"), )

score 0 · Answer 3 · answered Jul 03 '19 at 00:00

To get the max value per row:

=Today()-Max(B6:D6)

Alternatively, to get the max value based on the label of row (Name, Group, Etc.):

=ARRAYFORMULA(TODAY()-MAX(IF($A$6:$A=$A6,$B$6:$D,)))

The latter would be highly useful in the case of an additional sheet of aggregated + unique values.

Google Sheets - Number of days since the last date a range of cells

3 Answers3