Lookup formula to determine activity use

[jmac0027]

Hello,

My excel project is used to track activity based on date. The employee who has the "most days since last use" is given priority when the activity becomes available.

• The yellow highlighted cell represents the current date.
• The green highlighted cells calculates how many days it has been since last use. The yellow highlight represents the current date.

LOOKUP(2,1/(D5:P5>0),TODAY() - D2:P2)
The problem with the current formula is that entering data beyond the current date affects outcome. I am hoping someone can suggest a solution that only count from the current date backwards until a "1" is encountered.

I can't use vba or macros.

Thank you!!!

Excel Formula Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		# Days from current date	08/26/24	08/27/24	08/28/24	08/29/24	08/30/24	08/31/24	09/01/24	09/02/24	09/03/24	09/04/24	09/05/24	09/06/24	09/07/24
2			26	27	28	29	30	31	01	02	03	04	05	06	07
3			MON	TUE	WED	THU	FRI	SAT	SUN	MON	TUE	WED	THU	FRI	SAT
4	Employee #1	2			1	1	1					1	1	1
5	Employee #2	5	1	1	1						1		0
6	Employee #3	2				1	1						1	1
7	Employee #4	4										1
8	Employee #5	1	1												1
9	Employee #6	2		1		1	1					1		1
10	Employee #7	4		1	1						1	1
11	Employee #8	3	1		1						1		1
12	Employee #9	2				1	1						1	1
13	Employee #10	2				1							1	1
14	Employee #11	3				1	1						1
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LOOKUP(2,1/(C4:O4>0),TODAY() - C1:O1)
B5:B6	B5	=LOOKUP(2,1/(C5:O5>0),TODAY() - C1:O1)
B7	B7	=LOOKUP(2,1/(C7:O7>0),TODAY() - C1:O1)
B8	B8	=LOOKUP(2,1/(C8:O8>0),TODAY() - C1:O1)
B9	B9	=LOOKUP(2,1/(C9:O9>0),TODAY() - C1:N1)
B10	B10	=LOOKUP(2,1/(C10:O10>0),TODAY() - C1:O1)
B11	B11	=LOOKUP(2,1/(C11:O11>0),TODAY() - C1:O1)
B12	B12	=LOOKUP(2,1/(C12:O12>0),TODAY() - C1:O1)
B13	B13	=LOOKUP(2,1/(C13:O13>0),TODAY() - C1:O1)
B14	B14	=LOOKUP(2,1/(C14:O14>0),TODAY() - C1:O1)

 

[kvsrinivasamurthy]

Extend dates up to U column. If required to any column and change the formula accordingly.
In B4 copied down.

=LOOKUP(2,1/((C4:U4>0)*($C$1:$U$1<=TODAY())),TODAY() - $C$1:$U$1)

 

[jmac0027]

Works great! I modified slightly so current day not included: =LOOKUP(2,1/((C4:U4>0)*($C$1:$U$1<=(TODAY()-1))),(TODAY()-1) - $C$1:$U$1)

Really appreciate your help. I had originally tried XLOOKUP but never got it to work.

Cheers!

 

[jmac0027]

Hello I am again seeking the wisdom of the group. The adjacent column to the one listed above sums the range. However, I cannot figure out how to limit the range to the previous 20 days (including current day). Here is the formula I am currently using. =SUMIFS(H5:FD5,H2:FD2, “>=TODAY()-20)

However, it includes the previous 20 days plus everything in future dates. Again, I want to limit to today and 20 days in the past.

Thank you!

 

[kvsrinivasamurthy]

Try.

=SUMIFS(H5:FD5,H2:FD2, ">="&TODAY()-20,H2:FD2, "<="&TODAY())