CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-query
Harry Norman
Harry Norman

Reputation: 195

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

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

Upvotes: 4

Views: 2624

Answers (3)

Yaakov Bressler
Yaakov Bressler

Reputation: 12128

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.

Upvotes: 0

player0
player0

Reputation: 1

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"), )

0

Upvotes: 0

player0
player0

Reputation: 1

try like this:

=DAYS(TODAY(), D6)

0

or perhaps like this:

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

0

Upvotes: 4

Related Questions