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.

 

[BSALV]

i have problems with the visualisation of your idea.
What do you have for the moment and what do you want to achieve ?
Show it, if possible, with the XL2BB-tool.

 

[Mikec1012]

I know it was a lot and confusing - my apologies. So here's a screen shot of what I would like OR even possibly instead of 24 months as picture below, have it set for weeks. Then, when I plug in dates under D9 - E9 -- it would highlight all cells that fall within that date range.

So below...
D2 - this date would be a manually entered beginning date
C4-N4 and C5-N5 - Cells subtract 1 month from the previous cell to equate to 24 months

The data entered in D9-E9 -- formatting cell between the C4:N5 rage to highlight the cell IF it falls between that date range - format with a green cell highlight.

In the end, it would show me for example employment gaps/breaks for any cell between C4:N5 that are not filled in with green.

 

[Mikec1012]

I know it was a lot and confusing - my apologies. So here's a screen shot of what I would like OR even possibly instead of 24 months as picture below, have it set for weeks. Then, when I plug in dates under D9 - E9 -- it would highlight all cells that fall within that date range.

So below...
D2 - this date would be a manually entered beginning date
C4-N4 and C5-N5 - Cells subtract 1 month from the previous cell to equate to 24 months

The data entered in D9-E9 -- formatting cell between the C4:N5 rage to highlight the cell IF it falls between that date range - format with a green cell highlight.

In the end, it would show me for example employment gaps/breaks for any cell between C4:N5 that are not filled in with green.

View attachment 61074

Just noticed the typo in Employment History - haha!

 

[etaf]

does this work

i have used a countifs()

see the test section, just to show it working - not needed in final sheet , as conditional formatting works

If that works ok for you , and covers the range OK, not 100% it works in all conditions - so please try out all possibilities in your real data

then we can look at weeks - but would need more info for that - as in starting day of week etc - based on date entered ie will it be a sun-sat week - so a few examples would be needed

please try usng xl2bb

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	starting date		3/28/22
3
4			3/1/22	2/1/22	1/1/22	12/1/21	11/1/21	10/1/21	9/1/21	8/1/21	7/1/21	6/1/21	5/1/21	4/1/21
5			3/1/21	2/1/21	1/1/21	12/1/20	11/1/20	10/1/20	9/1/20	8/1/20	7/1/20	6/1/20	5/1/20	4/1/20
6
7
8				Start	End
9				7/7/20	11/1/20
10				1/1/21	5/25/21
11
12
13
14			Test
15			0	0	0	0	0	0	0	0	0	0	1	1
16			1	1	1	0	1	1	1	1	0	0	0	0
17
Sheet1

Cell Formulas
Range	Formula
C4	C4	=DATE(YEAR(C2),MONTH(C2),DAY(1))
D4:N5	D4	=DATE(YEAR(C4),MONTH(C4)-1,DAY(1))
C5	C5	=DATE(YEAR(C2)-1,MONTH(C2),DAY(1))
C15:N16	C15	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:N5	Expression	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)	text	NO

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[Mikec1012]

does this work

i have used a countifs()

see the test section, just to show it working - not needed in final sheet , as conditional formatting works

If that works ok for you , and covers the range OK, not 100% it works in all conditions - so please try out all possibilities in your real data

then we can look at weeks - but would need more info for that - as in starting day of week etc - based on date entered ie will it be a sun-sat week - so a few examples would be needed

please try usng xl2bb

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	starting date		3/28/22
3
4			3/1/22	2/1/22	1/1/22	12/1/21	11/1/21	10/1/21	9/1/21	8/1/21	7/1/21	6/1/21	5/1/21	4/1/21
5			3/1/21	2/1/21	1/1/21	12/1/20	11/1/20	10/1/20	9/1/20	8/1/20	7/1/20	6/1/20	5/1/20	4/1/20
6
7
8				Start	End
9				7/7/20	11/1/20
10				1/1/21	5/25/21
11
12
13
14			Test
15			0	0	0	0	0	0	0	0	0	0	1	1
16			1	1	1	0	1	1	1	1	0	0	0	0
17
Sheet1

Cell Formulas
Range	Formula
C4	C4	=DATE(YEAR(C2),MONTH(C2),DAY(1))
D4:N5	D4	=DATE(YEAR(C4),MONTH(C4)-1,DAY(1))
C5	C5	=DATE(YEAR(C2)-1,MONTH(C2),DAY(1))
C15:N16	C15	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:N5	Expression	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)	text	NO

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Oh man!! This is perfect.

Question for you... so I wanted to break this down in to weeks? How might that be done?

does this work

i have used a countifs()

see the test section, just to show it working - not needed in final sheet , as conditional formatting works

If that works ok for you , and covers the range OK, not 100% it works in all conditions - so please try out all possibilities in your real data

then we can look at weeks - but would need more info for that - as in starting day of week etc - based on date entered ie will it be a sun-sat week - so a few examples would be needed

please try usng xl2bb

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	starting date		3/28/22
3
4			3/1/22	2/1/22	1/1/22	12/1/21	11/1/21	10/1/21	9/1/21	8/1/21	7/1/21	6/1/21	5/1/21	4/1/21
5			3/1/21	2/1/21	1/1/21	12/1/20	11/1/20	10/1/20	9/1/20	8/1/20	7/1/20	6/1/20	5/1/20	4/1/20
6
7
8				Start	End
9				7/7/20	11/1/20
10				1/1/21	5/25/21
11
12
13
14			Test
15			0	0	0	0	0	0	0	0	0	0	1	1
16			1	1	1	0	1	1	1	1	0	0	0	0
17
Sheet1

Cell Formulas
Range	Formula
C4	C4	=DATE(YEAR(C2),MONTH(C2),DAY(1))
D4:N5	D4	=DATE(YEAR(C4),MONTH(C4)-1,DAY(1))
C5	C5	=DATE(YEAR(C2)-1,MONTH(C2),DAY(1))
C15:N16	C15	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:N5	Expression	=COUNTIFS($D$9:$D$14,"<="&C4,$E$9:$E$14,">="&C4)	text	NO

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

You. Have. Been. Awesome! That has worked. Silly question next.... As I want to make this a template type sheet, my starting date will be blank. How do I hide the data in the cells calculate the dates:

 

[Mikec1012]

Employment History Tracker.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:					TWO YEARS BACK:		12/31/97
3
4			01/00/00	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
5			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
6			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
7			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
8			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
9			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
10			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
11			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
12			###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########	###########
13
14		EMPLOYMENT HISTORY
15		EMPLOYER		START DATE	END DATE
16
17
18
19
20
21
22
23
24
25
26				02/25/22	03/28/22
27
28
Work History by Week

Cell Formulas
Range	Formula
N2	N2	=DATE(YEAR(I2) - 2, MONTH(I2), DAY(I2))
C4	C4	=I2
D4:N12	D4	=C4-7
C5:C12	C5	=N4-7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:N12	Expression	=COUNTIFS($D$16:$D$27,"<="&C4,$E$16:$E$27,">="&C4)	text	NO

 

[etaf]

have you fully tested the monthly version - for your real data , in case it throws up an issue

Weekly maybe more difficult depending on how we work out the dates, maybe we could use weeknum()
not sure yet

i'll have a think and play

you can use an IF( I2="", "" , ......

 

[etaf]

I'm not 100% sure on this , added the blank if no date added

but i think this may cause issues on some dates because of the start/end of week

again throughly check out and if errors - make sure you highlight specifically

also as before dropbox link - note these file do NOT stay on my dropbox for more than a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Emp_hist-ETAF-weekly.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	3/17/20
19				1/23/19	4/18/19
20
21
22
23
24
25
26
27		TEST AREA
28			1	1	1	1	1	1	1	1	1	1	1	1
29			1	1	1	1	1	1	0	0	0	0	0	0
30			0	0	0	0	0	0	0	0	0	0	0	0
31			0	1	1	1	1	1	1	1	1	1	1	1
32			1	1	0	0	0	0	0	0	0	0	0	0
33			0	0	0	0	0	0	0	0	0	0	0	0
34			0	0	0	0	0	0	0	1	1	1	1	0
35			0	0	0	0	0	0	0	0	0	0	0	0
36			0	0	0	0	0	0	0	0	0	0	0	0
37
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	=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Mikec1012]

I'm not 100% sure on this , added the blank if no date added

but i think this may cause issues on some dates because of the start/end of week

again throughly check out and if errors - make sure you highlight specifically

also as before dropbox link - note these file do NOT stay on my dropbox for more than a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Emp_hist-ETAF-weekly.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	3/17/20
19				1/23/19	4/18/19
20
21
22
23
24
25
26
27		TEST AREA
28			1	1	1	1	1	1	1	1	1	1	1	1
29			1	1	1	1	1	1	0	0	0	0	0	0
30			0	0	0	0	0	0	0	0	0	0	0	0
31			0	1	1	1	1	1	1	1	1	1	1	1
32			1	1	0	0	0	0	0	0	0	0	0	0
33			0	0	0	0	0	0	0	0	0	0	0	0
34			0	0	0	0	0	0	0	1	1	1	1	0
35			0	0	0	0	0	0	0	0	0	0	0	0
36			0	0	0	0	0	0	0	0	0	0	0	0
37
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	=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Awesome!!!

I was able to use this and transfer over to my master sheet. I then followed the formula and made the "TWO YEARS BACK" cell to hide as well pending the "STARTING DATE" cell. You are amazing!!