Extract the dates missing per item from a list of dates

Hi Freelanx. Welcome to the forum.

If I understand correctly try this:

With Template 1 data in A:D in the below, array enter this formula in G2 and fill down until you get blanks.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Then array enter this in F2 and fill down until you get blanks.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	Item	Start date	End date	Status		Item	Date
2	1	01/06/2018	04/06/2018	Check		1	02/06/2018
3	2	10/06/2018	14/06/2018	OK		1	03/06/2018
4	1	01/06/2018	04/06/2018	OK		1	05/06/2018
5	3	08/06/2018	12/06/2018	Check		1	06/06/2018
6						2	07/06/2018
7						2	09/06/2018
8						3	11/06/2018
9						3	13/06/2018

Thank you FlameRetired for your contribution, but I think I did not explain myself properly. I have uploaded a file as example to try to explain the problem in a better way.

I will explain the process again using this example:

"ITEMS" Tab: List of items available

"ENTRIES" Tab: List where employees will enter the items and the period of date in which the item is filled in. Here there are two status possible:

• OK: There will not be more changes.
• Check: In the future, there will be a change to OK (USING EXACTLY THE SAME DATES). Always will be from Check to OK, never in other way.

Here, the unique time in which is possible the overlapping of dates it is when there is a change in the status.


"DAYS" Tab: It is the same list than "ENTRIES Tab", but broken down per day. Now, it is automatic wih a formula that I inserted but I would like also if you can help me o improve it using an array formula.

Modification: Now it appear all the records inserted in the previous tab, but I would like to enter a formula that only recognize the last entries in order to not repeat the data and keep update the list. This changes is applicable only to the entries with Check status. So, in the example will disappear the data in red. (I re-check it and I did not put it in red, but it will be the first 4 rows of the table)

"DAYS MISSING PER DAY": Here it is the table with the days missing per item in a period of time. Now it is filled manually, but I need to insert a formula which fill the columns automatically and, as I said before, I do not know where to start.

In resume, I will need to improve the formula in the "DAYS" Tab and create a new one "DAYS MISSING PER DAY".

PS: I create the file in spanish, because Excel in the office it is in Spanish and I do not know how Excel works when it has to translate the formulas. If you have any problem to open it please let me know.

See if this does what you want.

The formulas I post are in American. I don't know the Spanish function names. Also these have argument separators ",". I would guess yours are ";".

It should all translate in the upload.

Edit Sucuri will not let me post the formulas and I do not know why. I will explain as best I can.

For:

DAYS" Tab:

Find a separate table in columns H:L.

The formula that returns IDs in H2 and filled down until you get blanks is an array formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The rest in that sheet are non array.

I took liberties with the layout of sheet 'DAYS MISSING PER ITEM'. I have not found a way to return repeating Item numbers in column A.

I opted to make a row F4:I4 of unique Item numbers. The formula in F5 must be array entered. Fill across column I and fill down until you get blanks.

The dates agree with your hand typed example.

Does this work for your purposes?

Oh yes. There is something I forgot to mention in the last post.

In column C of 'DAYS MISSING PER ITEM' I put a formula. It has no relevance to the rest of the formulas. It was a way for me to check my formulas as I was building them. You can deleted them if you wish.

I mean, in the tab in red there is one cell in red where should go a formula which I can fill down and obtain the same list than in the right.

I have not been interpreting instructions correctly.

I need your help. Please include in the upload a few hand typed dates that you expect in the red tab. With that information maybe I will be able to understand.

Try array entering (Ctrl + Shift + Enter) this in C5 'DAYS MISSING PER ITEM'. It will fill down automatically.

Formula:

Please Login or Register  to view this content.

The proposed solution employs a helper table (array) which may be moved and/or hidden for aesthetic purposes.
A few cells of the array will need to be filled manually, namely the item numbers (I1:M1) and the number of the row that has the column headers (4 in cell H3)
The first date (cell H4) is populated using: =C2
The rest of the dates (H5:H20)are populated using: =IF(SUM(MAX(H$4:H4),1)>F$2,"",SUM(H4,1))
I2:M2 are populated using: =COUNTIFS(I4:I26,TRUE)
I3:M3 are populated using: =SUM(H3,I2)
I4:M20 are populated using:

Formula:

Please Login or Register  to view this content.

A5:A46 are populated using:

Formula:

Please Login or Register  to view this content.

B5:B46 are populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.