formula for missed duew date by operator

Mike LH

Well-known Member

Joined

Mar 17, 2015

Messages

566

• Apr 25, 2015

• #2

Hi,

How do we tell from that whether an item has been completed, all it seems to tell us is that the item was due on 20 Apr?

 

Upvote 0

K

kumar1803

Board Regular

Joined

Jan 7, 2015

Messages

110

• Apr 25, 2015

• #3

Sorry I missed the status column . It is after due date. We are marking them complete if completed prior to due date.

 

Upvote 0

Mike LH

Well-known Member

Joined

Mar 17, 2015

Messages

566

• Apr 25, 2015

• #4

Hi,

So let's say that when completed you enter completed in Col F. you can use this where G1 is the name you want to count.


=SUMPRODUCT((D2:D20=G1)*(F2:F20<>"Completed")*(E2:E20<TODAY()))

 

Upvote 0

K

kumar1803

Board Regular

Joined

Jan 7, 2015

Messages

110

• Apr 25, 2015

• #5

I tried the above formula. I got "0" even for in progress. Keep in mind I would to like to see only those that are missed due date and still in progress.

Date	Item	Description	Operator	Due by	Status	Formula
3/25/2015	1	abc	Man	4/15/2015	Completed	0
4/15/2015	2	xyz	SHE	4/19/2015	Inprogress	0
3/15/2015	3	ttt	hes	4/5/2015	Inprogress	0

<colgroup><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,165

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Apr 25, 2015

• #6

@MikeLH

Hi Mike, the board interprets the < symbol as a Html tag and so to get it to display correctly put a space after the < and ask the OP to remove the space.

=SUMPRODUCT((D2:D20=G1)*(F2:F20<>"Completed")*(E2:E20< TODAY()))

Click to expand...

@kumar1803 You need another column/cell to lookup the operator names

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Item	Description	Operator	Due by	Status	Operator LU	formula
2	3/25/2015	1	abc	Man	15/04/2015	Completed	hes	1
3	4/15/2015	2	xyz	SHE	19/04/2015	Inprogress	SHE	1
4	3/15/2015	3	ttt	hes	05/04/2015	Inprogress	Man	0
Sheet1

 

Upvote 0

K

kumar1803

Board Regular

Joined

Jan 7, 2015

Messages

110

• Apr 25, 2015

• #7

Hi Mark

Thank you for quick response. In the example above, I see you have counted both "Completed" and :InProgress" . I would like to count only those " InProgress"

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,165

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Apr 25, 2015

• #8

Re-read it. It references the names in column G only as the Operator name to look up.
Man and SHE in Column D are both incomplete in column F and so have a count of 1. Man in column D is Completed in column F and so is 0.

 

Last edited: Apr 25, 2015

Upvote 0

Mike LH

Well-known Member

Joined

Mar 17, 2015

Messages

566

• Apr 25, 2015

• #9

MARK858 said:

@MikeLH

Hi Mike, the board interprets the < symbol as a Html tag and so to get it to display correctly put a space after the < and ask the OP to remove the space.



@kumar1803 You need another column/cell to lookup the operator names

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Item	Description	Operator	Due by	Status	Operator LU	formula
2	3/25/2015	1	abc	Man	15/04/2015	Completed	hes	1
3	4/15/2015	2	xyz	SHE	19/04/2015	Inprogress	SHE	1
4	3/15/2015	3	ttt	hes	05/04/2015	Inprogress	Man	0
Sheet1

Click to expand...

Hmmm,

Well I just learnt something thanks.

 

Upvote 0