Formula Help - Days since last event

J

jrsomerville

New Member
Joined
Mar 28, 2019
Messages
5
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! :cool:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Days Since Last Visit[/TD]
[TD]4/1/19[/TD]
[TD]4/2/19[/TD]
[TD]4/3/19[/TD]
[TD]4/4/19[/TD]
[TD]4/5/19[/TD]
[TD]4/6/19[/TD]
[TD]4/7/19[/TD]
[TD]4/8/19[/TD]
[TD]4/9/19[/TD]
[TD]4/10/19[/TD]
[TD]4/11/19[/TD]
[TD]4/12/19[/TD]
[TD]4/13/19[/TD]
[TD]4/14/19[/TD]
[TD]4/15/19[/TD]
[TD]4/16/19[/TD]
[TD]4/17/19[/TD]
[TD]4/18/19[/TD]
[/TR]
[TR]
[TD]Target[/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]
[TD][/TD]
[/TR]
[TR]
[TD]Walmart[/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]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kmart[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Petco[/TD]
[TD][/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]
[TD]Trader Joes[/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][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about


Excel 2013/2016
ABCDEFGHIJKLMNOPQRST
1SiteDays Since Last Visit12 Mar13 Mar14 Mar15 Mar16 Mar17 Mar18 Mar19 Mar20 Mar21 Mar22 Mar23 Mar24 Mar25 Mar26 Mar27 Mar28 Mar29 Mar
2Target1 daysxxx
3Walmart0 daysxxx
4Kmart5 daysxx
5Petco3 daysxxx
6Trader Joes4 daysxxx
Sheet1
Cell Formulas
RangeFormula
B2=TODAY()-LOOKUP(2,1/(C2:T2<>""),$C$1:$T$1) & " days"
 
Last edited:
Upvote 0
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 />
 
Upvote 0
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>[TABLE="width: 0"]
<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>[TR]
[TD="align: center"]Store[/TD]
[TD="align: center"]Pad[/TD]
[TD="align: center"]Region[/TD]
[TD="align: center"]Store Name[/TD]
[TD="align: center"]County[/TD]
[TD="align: center"]Address[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Zip[/TD]
[TD="align: center"]2017 Contractor[/TD]
[TD="align: center"]2018 Contractor[/TD]
[TD="align: center"]2019 Contractor[/TD]
[TD="align: center"]Grass?[/TD]
[TD="align: center"]Days Since Last Visit[/TD]
[TD="align: right"]4/1[/TD]
[TD="align: right"]4/2[/TD]
[TD="align: right"]4/3[/TD]
[TD="align: right"]4/4[/TD]
[TD="align: right"]4/5[/TD]
[TD="align: right"]4/6[/TD]
[TD="align: right"]4/7[/TD]
[TD="align: right"]4/8[/TD]
[TD="align: right"]4/9[/TD]
[TD="align: right"]4/10[/TD]
[TD="align: right"]4/11[/TD]
[TD="align: right"]4/12[/TD]
[TD="align: right"]4/13[/TD]
[TD="align: right"]4/14[/TD]
[/TR]
[TR]
[TD="align: center"]3014[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"][/TD]
[TD="bgcolor: #92D050"][/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3182[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"][/TD]
[TD="bgcolor: #92D050"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3045[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"][/TD]
[TD="bgcolor: #92D050"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
=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"
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell		Formula
N2{=TODAY()-MAX((IF(O2:AF2<>"",$O$1:$AF$1))) & " days "}

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

Array formula
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
388
bobsan42
bobsan42
L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
274
Fluff
Fluff
G
  • Question Question
Filling cells with multiple " IF " & " THEN " in the formula
Replies
0
Views
267
Guy Boot
G
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
A
  • Solved
Number of Weeks Between First and Last of a List of Dates
Replies
7
Views
195
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top