Mikec1012

New Member
Joined
Oct 24, 2017
Messages
28
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

New Member
Joined
Oct 24, 2017
Messages
28
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?

1648503517934.png
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,117
Office Version
  1. 365
Platform
  1. MacOS
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 ?



Emp_hist-ETAF-weekly-1.xlsx
ABCDEFGHIJKLMNO
1
2Please enter your starting date to the right. This will trace back 104 weeks for employment tracking. STARTING DATE:1/1/20TWO YEARS BACK:1/1/18
3
41/1/2012/25/1912/18/1912/11/1912/4/1911/27/1911/20/1911/13/1911/6/1910/30/1910/23/1910/16/19
510/9/1910/2/199/25/199/18/199/11/199/4/198/28/198/21/198/14/198/7/197/31/197/24/19
67/17/197/10/197/3/196/26/196/19/196/12/196/5/195/29/195/22/195/15/195/8/195/1/19
74/24/194/17/194/10/194/3/193/27/193/20/193/13/193/6/192/27/192/20/192/13/192/6/19
81/30/191/23/191/16/191/9/191/2/1912/26/1812/19/1812/12/1812/5/1811/28/1811/21/1811/14/18
911/7/1810/31/1810/24/1810/17/1810/10/1810/3/189/26/189/19/189/12/189/5/188/29/188/22/18
108/15/188/8/188/1/187/25/187/18/187/11/187/4/186/27/186/20/186/13/186/6/185/30/18
115/23/185/16/185/9/185/2/184/25/184/18/184/11/184/4/183/28/183/21/183/14/183/7/18
122/28/182/21/182/14/182/7/181/31/181/24/181/17/181/10/181/3/1812/27/1712/20/1712/13/17
13
14EMPLOYMENT HISTORY
15EMPLOYERSTART DATEEND DATE
16
176/1/187/1/18
189/1/1911/17/19
191/23/194/18/19
20
21
22
23
24
25
26
27TEST AREA
28FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
29FALSEFALSEFALSEFALSEFALSEFALSETRUETRUETRUETRUETRUETRUE
30TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
31TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
32FALSEFALSETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
33TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
34TRUETRUETRUETRUETRUETRUETRUEFALSEFALSEFALSEFALSEFALSE
35FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
36FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
37
38min6/1/18
39Max11/17/19
40
41
Sheet1
Cell Formulas
RangeFormula
N2N2=DATE(YEAR(I2) - 2, MONTH(I2), DAY(I2))
C4C4=IF(I2="","",I2)
D4:N12D4=IF(C4="","",C4-7)
C5:C12C5=IF(N4="","",N4-7)
C28:N36C28=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,"<>"))
C38C38=MINIFS($D$17:$D$23,$D$17:$D$23,"<>")
C39C39=MAXIFS($E$18:$E$23,$E$18:$E$23,"<>")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:N12Expression=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,"<>"))textNO
C38:C39Expression=COUNTIFS($D$17:$D$23,"<="&C38,$E$17:$E$23,">="&C38)textNO
C4:N12Expression=COUNTIFS($D$17:$D$23,"<="&C4,$E$17:$E$23,">="&C4)textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,117
Office Version
  1. 365
Platform
  1. MacOS
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 ?
 

Forum statistics

Threads
1,175,456
Messages
5,897,525
Members
434,659
Latest member
Fityi

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