Formula or VBA?

[Christopherc2371]

I've been thinking about this for a few weeks now and can't come up with a workable solution. I've searched for weeks trying to find an answer but I'm officially stumped. Any help would be huge!

Situation: I have a list of names with a start and an end date for performance reports as shown below. I'm trying to make this excel workbook tell me if the end date and the start date for different rows are the same or if they create a gap or overlap for the same people. I've tried conditional formatting, formulas, and any number of VBA solutions but I can't figure it out.

1. Smith end date to 3 Smith start dtae creates a gap.
2. John end date to 4 John start date creates a overlap
5. Doe start date to 5 Doe end date misses a 6/1/22 report

I'm also trying to get it to tell me if I miss a report in the middle based on report hierarchy and date. If that's a step too deep and it's not possible, I'll accept defeat and keep running.

Thank you to all that read this and all suggestions are welcome!

 

[Peter_SSs]

Welcome to the MrExcel board!

I would recommend that you provide the sample data and the expected results (manually entered) with XL2BB so that users can see exactly what result & layout you are after and can copy your sample data for testing (unable to copy from a picture).

Make sure that you provide enough sample data to demonstrate all the different results that might occur.

 

[Christopherc2371]

Welcome to the MrExcel board!

I would recommend that you provide the sample data and the expected results (manually entered) with XL2BB so that users can see exactly what result & layout you are after and can copy your sample data for testing (unable to copy from a picture).

Make sure that you provide enough sample data to demonstrate all the different results that might occur.

My apologies, I've never posted before. I'll install and repost! Thank you

 

[Peter_SSs]

I'll install and repost!

OK, no problem. Be sure to post in this thread again though, don't start a new one.

 

[Christopherc2371]

FITREP Tracker.xlsx
	A	B	C	D	E	F
1	Number	Rank	Name	Start Date	End Date	Report
2	1	Sgt	Smith, Bobby M.	1/1/22	6/1/22	Transfer
3	2	SSgt	John, Theodore	1/1/22	7/2/22	Transfer
4	3	Sgt	Smith, Bobby M.	6/5/22	12/31/22	Annual
5	4	SSgt	John, Theodore	6/5/22	12/31/22	Annual
6	5	GySgt	Doe, Charles	4/1/22	12/31/22	Promotion
7
8	1		Smith, Bobby M.	1/1/22	6/1/22	Transfer
9	3		Smith, Bobby M.	6/5/22	12/31/22	Annual
10	2		John, Theodore	1/1/22	7/2/22	Transfer
11	4		John, Theodore	6/5/22	12/31/22	Annual
12
13					Required Reports
14					3/31/22	Sgt Annual
15					12/31/22	SSgt Annual
16					6/30/22	GySgt Annual
Sheet1

 

[Christopherc2371]

Report 1 for Sgt Smith and report 3 for Sgt Smith create a date gap between the end of report 1 and the start of report 2.
Report 2 for SSgt John and report 4 for SSgt John create an overlap between the end of report 2 and the start of report 4.
Report 5 for GySgt Doe skipped the required annual required report for 6/30/22 between the 4/1/22 start and 12/31/22 end date.

Any way to highlight, format, or announce them in a separate cell as a date gap, date overlap, or a missed report is my goal. A separate cell with a formula that lists the error or validation check failed would be perfect. Any suggestions would be great!