need some help

[sshah0349]

Hi guys,

I need a quick way to find rows where there is a 1 year lapse in "Created Date" but specific to the account name. In the example below, I have data for the 26DotTwo account, however, the master file I'm working with has hundreds of accounts. Is there any sort of IF combo formula that can be used to help highlight this?

The last row is the date we first engaged with this account, and above it are additional dates we worked with them. It's all sorted so the most recent date is shown at the top, and the oldest date at the bottom. What i'm trying to find are dates where there is a gap of 1 year or more.

For example, the 3rd to last row would be an example of this. We worked with them on 4/1/2013 where previously we last worked with them on 9/1/2010 which is almost a 3 year gap.

Does that make sense? Any help to automate this would be greatly appreciated! Thanks!

Account Name	Created Date
26 Dot Two	3/4/2015
26 Dot Two	1/27/2015
26 Dot Two	12/31/2014
26 Dot Two	12/4/2014
26 Dot Two	10/14/2014
26 Dot Two	9/17/2014
26 Dot Two	8/19/2014
26 Dot Two	10/8/2013
26 Dot Two	8/5/2013
26 Dot Two	7/24/2013
26 Dot Two	5/28/2013
26 Dot Two	4/1/2013
26 Dot Two	9/1/2010
26 Dot Two	8/30/2010

 

[jasonb75]

This will show the the number of days where the gap is greater than 1 year, is this something that you can work with?

Total Hours (version 1).xlsb
	A	B	C
1	Account Name	Created Date
2	26 Dot Two	04/03/2015
3	26 Dot Two	27/01/2015
4	26 Dot Two	31/12/2014
5	26 Dot Two	04/12/2014
6	26 Dot Two	14/10/2014
7	26 Dot Two	17/09/2014
8	26 Dot Two	19/08/2014
9	26 Dot Two	08/10/2013
10	26 Dot Two	05/08/2013
11	26 Dot Two	24/07/2013
12	26 Dot Two	28/05/2013
13	26 Dot Two	01/04/2013
14	26 Dot Two	01/09/2010	943
15	26 Dot Two	30/08/2010
Sheet7

Cell Formulas
Range	Formula
C2:C15	C2	=IFERROR(--TEXT(MINIFS(B$1:B1,A$1:A1,A2)-B2,"[>365]0;;;"),"")

 

[sshah0349]

Thanks so much for the quick reply! I probably could have worded the question a bit better - but basically if there could be a third column added that shows the gap in years from the previous date (one row below). See below - i did this pretty quick using the YEARFRAC formula, but the main sheet i'm working on has around 4K rows with tons of different accounts so im looking for a formula that can help automate this.

Thanks again! any help is greatly appreciated

Account Name	Created Date	Year Gap
26 Dot Two	3/4/2015​	0.10
26 Dot Two	1/27/2015​	0.07
26 Dot Two	12/31/2014​	0.07
26 Dot Two	12/4/2014​	0.14
26 Dot Two	10/14/2014​	0.07
26 Dot Two	9/17/2014​	0.08
26 Dot Two	8/19/2014​	0.86
26 Dot Two	10/8/2013​	0.18
26 Dot Two	8/5/2013​	0.03
26 Dot Two	7/24/2013​	0.16
26 Dot Two	5/28/2013​	0.16
26 Dot Two	4/1/2013​	2.58
26 Dot Two	9/1/2010​	0.01
26 Dot Two	8/30/2010​

 

[sshah0349]

I guess the tough part is making the formula specific to just the range of the individual account

 

[jasonb75]

Something like this?

Total Hours (version 1).xlsb
	A	B	C
1	Account Name	Created Date
2	26 Dot Two	04/03/2015	0.10
3	26 Dot Two	27/01/2015	0.08
4	26 Dot Two	31/12/2014	0.08
5	26 Dot Two	04/12/2014	0.14
6	26 Dot Two	14/10/2014	0.08
7	26 Dot Two	17/09/2014	0.08
8	26 Dot Two	19/08/2014	0.86
9	26 Dot Two	08/10/2013	0.18
10	26 Dot Two	05/08/2013	0.03
11	26 Dot Two	24/07/2013	0.16
12	26 Dot Two	28/05/2013	0.16
13	26 Dot Two	01/04/2013	2.58
14	26 Dot Two	01/09/2010	0.00
15	26 Dot Two	30/08/2010	0.00
Sheet7

Cell Formulas
Range	Formula
C2:C14	C2	=YEARFRAC(VLOOKUP(A2,A3:$B$15,2,0),B2)
C15	C15	=YEARFRAC(VLOOKUP(A15,A$15:$B16,2,0),B15)