communiction log

dmj120

Active Member
Joined
Jan 5, 2010
Messages
275
Office Version
  1. 2019
  2. 2010
I'd like to create a communication log of several customers (at several accounts) which calculates the days since last date contacted.

This table will be a running log of customers. I've used a max() formula for finding the "last date" but with this, I'd need to find the last date and the 'next to last date' then subtract the two - I'm then going to average all the "days since last contacted" to get running average of how often a particular customer is contacted.

This is what I've used in the past to find the most recent date, but I'm stuck with looking back the closest previous date to subtract.
{=MAX(IF('SM-DM Jan2013-4.20-16'!$X$1:$X$13827=D15,'SM-DM Jan2013-4.20-16'!$M$1:$M$13827))}

any ideas or suggestions on how i can do this? There will eventually be a summary table with looks up the last entry for each facility/person so I can readily see the date last contacted, how many days since the last contact, and average time a particular customer is contacted --- there's some administrative needs behind this.

An example table:
Account
Person
Date Contacted
Days Since last contact
Average
Facility 1
Vickie
11/15/16
Facility 2
Bob
12/1/16
Facility 1
Vickie
12/1/16
16
16
Facility 1
Vickie
12/10/16
9
12.5
Facility 2
Bob
12/10/16
9
9

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,191,579
Messages
5,987,428
Members
440,096
Latest member
yanaungmyint

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
Top