Date Tracking

[Mikec1012]

Hello all!

So here’s my scenario and/or “is it possible” - I work for a mortgage lender and I’m trying to find an excel spreadsheet or create one that would highlight cells based off employment dates. This would also help to show overlapping or then even show no formatting or highlight red if there is a gap or no employment for that week/month.

I would have two rows (12 cells each) both representing a cell for each month dating back / showing a 24 month window.

My date would be a Start Date and and End Date.

Need a formula or method that would then highlight the months that fall between the start/end date. So…
[A]
[1] START END
[2] 05/05/2020 12/01/2020
[3] 06/09/2020 03/01/2021
[4]
I would need to be able to use/add rows as needed based off how many employers one has between a 2 year window.

The formula would highlight cells that correspond with:
MAY | JUNE | JULY | AUGUST … to DECEMBER
And then overlap highlighting again and highlight out to MARCH

If possible even, to take a step further to break it down to 52 weeks - ROW 1 (A-BA) and ROW 2 (A-BA)

Not to throw more in to the mix - have a cell that you would plug in the date (not a TODAY()) but date of their application and then the 12 columns or 52 columns auto calculates back to give me a 24 month/2 year coverage.

Whew. A lot. I know but thank you.

 

[Mikec1012]

Soooo I hate to bother BUT one more question if I may... With the following, is there a way to highlight RED any cells that would be a gap between the dates entered?

 

[etaf]

how about
using
=AND(COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)=0, C4>=MINIFS($D$17:$D$23,$D$17:$D$23,"<>"), C4<=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>"))
so we are still using the count
ONLY this time testing if zero
=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)=0

Now we want to find the earliest start date and latest start date , so we do not include those dates - so we only identify the gaps between the earlist date recorded and latest date recorded
C4>=MINIFS($D$17:$D$23,$D$17:$D$23,"<>"), C4<=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>"))

we are using MINIFS , as we do not want to include blank cells as they record as 1/1/1900

does this work for you ?

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Emp_hist-ETAF-weekly-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Please enter your starting date to the right. This will trace back 104 weeks for employment tracking.					STARTING DATE:		1/1/20			TWO YEARS BACK:		1/1/18
3
4			1/1/20	12/25/19	12/18/19	12/11/19	12/4/19	11/27/19	11/20/19	11/13/19	11/6/19	10/30/19	10/23/19	10/16/19
5			10/9/19	10/2/19	9/25/19	9/18/19	9/11/19	9/4/19	8/28/19	8/21/19	8/14/19	8/7/19	7/31/19	7/24/19
6			7/17/19	7/10/19	7/3/19	6/26/19	6/19/19	6/12/19	6/5/19	5/29/19	5/22/19	5/15/19	5/8/19	5/1/19
7			4/24/19	4/17/19	4/10/19	4/3/19	3/27/19	3/20/19	3/13/19	3/6/19	2/27/19	2/20/19	2/13/19	2/6/19
8			1/30/19	1/23/19	1/16/19	1/9/19	1/2/19	12/26/18	12/19/18	12/12/18	12/5/18	11/28/18	11/21/18	11/14/18
9			11/7/18	10/31/18	10/24/18	10/17/18	10/10/18	10/3/18	9/26/18	9/19/18	9/12/18	9/5/18	8/29/18	8/22/18
10			8/15/18	8/8/18	8/1/18	7/25/18	7/18/18	7/11/18	7/4/18	6/27/18	6/20/18	6/13/18	6/6/18	5/30/18
11			5/23/18	5/16/18	5/9/18	5/2/18	4/25/18	4/18/18	4/11/18	4/4/18	3/28/18	3/21/18	3/14/18	3/7/18
12			2/28/18	2/21/18	2/14/18	2/7/18	1/31/18	1/24/18	1/17/18	1/10/18	1/3/18	12/27/17	12/20/17	12/13/17
13
14		EMPLOYMENT HISTORY
15		EMPLOYER		START DATE	END DATE
16
17				6/1/18	7/1/18
18				9/1/19	11/17/19
19				1/23/19	4/18/19
20
21
22
23
24
25
26
27		TEST AREA
28			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
29			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
30			TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
31			TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
32			FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
33			TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
34			TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
35			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
36			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
37
38		min	6/1/18
39		Max	11/17/19
40
41
Sheet1

Cell Formulas
Range	Formula
N2	N2	=DATE(YEAR(I2) - 2, MONTH(I2), DAY(I2))
C4	C4	=IF(I2="","",I2)
D4:N12	D4	=IF(C4="","",C4-7)
C5:C12	C5	=IF(N4="","",N4-7)
C28:N36	C28	=AND(COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)=0, C4>=MINIFS($D$17:$D$23,$D$17:$D$23,"<>"), C4<=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>"))
C38	C38	=MINIFS($D$17:$D$23,$D$17:$D$23,"<>")
C39	C39	=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:N12	Expression	=AND(COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)=0, C4>=MINIFS($D$17:$D$23,$D$17:$D$23,"<>"), C4<=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>"))	text	NO
C38:C39	Expression	=COUNTIFS($D$17:$D$23,"<="&C38,$E$17:$E$23,">="&C38)	text	NO
C4:N12	Expression	=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)	text	NO

 

[etaf]

if its between the start date and end date on the list - then basically thats everything that is not green
so thats just using
=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)=0
now every thing that is not green would be red , as the dates start from the startdate anyway - regardless of the employment history
my previous post was based on the employment history dates -

Is that what you want ?