Identify missing rows by date per ID

[reg59]

Hello,

I need a way to identify where dates are missing per ID. If ID002 fills out a survey on 9/6, 9/7, 9/8, 9/9, 9/12, 9/13, I want excel to create blank rows between 9/9 and 9/12 to represent the missing data from 9/10 and 9/11. This spreadsheet will eventually have 300+ IDs filling our information for 300 days, and the people will not be starting on the same day, so I need a quick way to see if people are not completing the surveys daily.

I tried to upload the file using XL2BB, but it is being blocked by my company.

 

[kevin9999]

If you're OK using conditional formatting, then the following should work as long as the data is sorted first by ID then date.

Book1
	A	B
1	Recorded date	ID
2	Recorded date	ID
3	9/4/2023 0:00	001
4	9/5/2023 0:00	001
5	9/7/2023 0:00	001
6	9/9/2023 0:00	001
7	9/10/2023 0:00	001
8	9/11/2023 0:00	001
9	9/12/2023 0:00	001
10	9/13/2023 0:00	001
11	9/14/2023 0:00	001
12	9/15/2023 0:00	001
13	9/5/2023 0:00	002
14	9/6/2023 0:00	002
15	9/7/2023 0:00	002
16	9/8/2023 0:00	002
17	9/9/2023 0:00	002
18	9/12/2023 0:00	002
19	9/13/2023 0:00	002
20	9/5/2023 0:00	003
21	9/6/2023 0:00	003
22	9/7/2023 0:00	003
23	9/8/2023 0:00	003
24	9/9/2023 0:00	003
25	9/10/2023 0:00	003
26	9/11/2023 0:00	003
27	9/12/2023 0:00	003
28	9/13/2023 0:00	003
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:B28	Expression	=AND(OR($B3=$B2,$B3=$B4),(OR(DAY($A3)-DAY($A2)>1,DAY($A4)-DAY($A3)>1)))	text	YES