Formula Help - Days since last event

[jrsomerville]

Hi - I was hoping somebody could help me out with an excel formula. I'm in operations at a landscaping company, and I'd like a better way to keep track of how many days it's been since the property was last serviced. Below is an example of the spreadsheet I have. Let's say today's hypothetical date is 4/17/19. I would like the "days since last visit" column to auto-populate "1 day" for Target, "0 days" for Walmart, "5 days" for Kmart, "3 days" for Petco, and "4 days" for Trader Joes. The dates continue through the end of the season which is 10/31/19 so any time I enter an X on a more current date, I would like it to know to change to "days since last visit" column. Thanks in advance for any help!

Site

Days Since Last Visit

4/1/19

4/2/19

4/3/19

4/4/19

4/5/19

4/6/19

4/7/19

4/8/19

4/9/19

4/10/19

4/11/19

4/12/19

4/13/19

4/14/19

4/15/19

4/16/19

4/17/19

4/18/19

Target

x

x

x

Walmart

x

x

x

Kmart

x

x

Petco

x

x

x

Trader Joes

x

x

x

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Site	Days Since Last Visit	12 Mar	13 Mar	14 Mar	15 Mar	16 Mar	17 Mar	18 Mar	19 Mar	20 Mar	21 Mar	22 Mar	23 Mar	24 Mar	25 Mar	26 Mar	27 Mar	28 Mar	29 Mar
2	Target	1 days	x							x								x
3	Walmart	0 days		x							x								x
4	Kmart	5 days				x								x
5	Petco	3 days			x					x						x
6	Trader Joes	4 days	x					x							x
Sheet1

Cell Formulas
Range	Formula
B2	=TODAY()-LOOKUP(2,1/(C2:T2<>""),$C$1:$T$1) & " days"

 

[DanteAmor]

It can also be with the following array formula.

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself



<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:71px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td></tr><tr style="height:39px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; text-align:center; ">Site</td><td style="background-color:#92d050; text-align:center; ">Days Since Last Visit</td><td style="background-color:#92d050; text-align:center; ">12 mar</td><td style="background-color:#92d050; text-align:center; ">13 mar</td><td style="background-color:#92d050; text-align:center; ">14 mar</td><td style="background-color:#92d050; text-align:center; ">15 mar</td><td style="background-color:#92d050; text-align:center; ">16 mar</td><td style="background-color:#92d050; text-align:center; ">17 mar</td><td style="background-color:#92d050; text-align:center; ">18 mar</td><td style="background-color:#92d050; text-align:center; ">19 mar</td><td style="background-color:#92d050; text-align:center; ">20 mar</td><td style="background-color:#92d050; text-align:center; ">21 mar</td><td style="background-color:#92d050; text-align:center; ">22 mar</td><td style="background-color:#92d050; text-align:center; ">23 mar</td><td style="background-color:#92d050; text-align:center; ">24 mar</td><td style="background-color:#92d050; text-align:center; ">25 mar</td><td style="background-color:#92d050; text-align:center; ">26 mar</td><td style="background-color:#92d050; text-align:center; ">27 mar</td><td style="background-color:#92d050; text-align:center; ">28 mar</td><td style="background-color:#92d050; text-align:center; ">29 mar</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Target</td><td style="text-align:center; ">1 days </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Walmart</td><td style="text-align:center; ">0 days </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Kmart</td><td style="text-align:center; ">5 days </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Petco</td><td style="text-align:center; ">3 days </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Trader Joes</td><td style="text-align:center; ">4 days </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=TODAY()-MAX((IF(C2:T2<>"",$C$1:$T$1))) & " days "}</td></tr></table></td></tr></table> <br /><br />

 

[jrsomerville]

Thank you so much for your quick reply. I'm sorry to be a huge pain, but I've copy and pasted how my spreadsheet is set up exactly with "Store" being column A, and therefore the "days since last visit" column would be N, etc.. Store is also row 1, store 3014 is row 2, etc.. I wish I knew enough about excel to be able to take the template of your formula and apply it to my situation but I don't. Would you be able to apply the formula you gave to this? I really appreciate it!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Store

Pad

Region

Store Name

County

Address

City

State

Zip

2017 Contractor

2018 Contractor

2019 Contractor

Grass?

Days Since Last Visit

4/1

4/2

4/3

4/4

4/5

4/6

4/7

4/8

4/9

4/10

4/11

4/12

4/13

4/14

3014

#N/A

3182

3045

<colgroup><col style="width: 178px"><col width="45"><col width="77"><col width="191"><col width="78"><col width="202"><col width="176"><col width="84"><col width="47"><col width="111"><col width="111"><col width="149"><col width="77"><col width="77"><col width="29"><col width="29"><col width="29"><col width="29"><col width="29"><col width="29"><col width="29"><col width="29"><col width="29"><col width="37"><col width="37"><col width="37"><col width="37"><col width="37"></colgroup><tbody>
</tbody>

 

[Fluff]

Try
=TODAY()-LOOKUP(2,1/(O2:AA2<>""),$O$1:$AA$1) & " days"
Change the value in blue to the first column to look at (guessing column O)
& change the red to the last column to look at

 

[jrsomerville]

=TODAY()-LOOKUP(2,1/(O2:AA2<>""),$O$1:$AA$1) & " days"

It says "Did not find value '2' in LOOKUP evaluation." and is referring to the 2 after the first "AA"

 

[Fluff]

Did you type the formula in, or copy/paste?
As it works for me, also I have never come across an error message like that before.

 

[jrsomerville]

I used this spreadsheet in Google Sheet and downloaded and opened it in Excel and it works there. Thank you and sorry for the trouble!

Another question you might be able to help me with: Is there a formula to change the cell fill color to red when the cell changes to "8 days" and stays red until serviced?

 

[DanteAmor]

Cell Formula N2 {=TODAY()-MAX((IF(O2:AF2<>"",$O$1:$AF$1))) & " days "} <tbody> </tbody>

<tbody>
</tbody>

Array formula
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jrsomerville]

Thank you, I combined both tips and made the formula "=arrayformula(TODAY()-LOOKUP(2,1/(O2:HU2<>""),$O$1:$HU$1) & " days")" and it works perfectly in Google Sheets now.