Comparing dates and names to get a number value

[sdezy]

Hi All
I am completely new to excel and this forum. I am a builder and I am trying to create a spreadsheet to track my jobs and workers. I have five columns, one with dates job allocated to workers, one with dates job is due for completion, one for which worker the job was allocated to, one for dates job actually completed and one for status of the job (in progress, on hold,completed etc) I want to compare the date due to the current date to track when a job is overdue and to whom the overdue job has been allocated. The results I would like listed at the top of the spreadsheet where I have the lists of names of the workers. E.G. John Smith: 2 (overdue jobs) I hope I have explained myself clearly. Thanks for any help.

Sorry I am using windows 7 and excel 2007.

 

[Jerry Sullivan]

Hi and Welcome,

I'd suggest you make an additional column to flag the rows with the overdue projects. The forumula compare due date to today's date and check to see if the status is "Completed".

Excel Workbook
	A	B	C	D	E	F
6	Allocated	Due	Worker	Completed	Status	Overdue?
7	22/02/2011	30/04/2011	John Smith		In Progress
8	22/02/2011	15/03/2011	John Smith		In Progress	Overdue
9	22/02/2011	15/03/2011	Jane Doe	12/04/2011	Completed
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F7	=IF(AND(E7<>"Completed",B7< font>),"Overdue","")
F8	=IF(AND(E8<>"Completed",B8< font>),"Overdue","")
F9	=IF(AND(E9<>"Completed",B9< font>),"Overdue","")

Then use the COUNTIFS function to total how many "Overdue" projects each worker has:

Excel Workbook
	E	F
1		Overdue Jobs
2	John Smith	1
3	Jane Doe	0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F2	=COUNTIFS(C$7:C$1000,E2,F$7:F$1000,"Overdue")
F3	=COUNTIFS(C$7:C$1000,E3,F$7:F$1000,"Overdue")

Good luck!

 

[sdezy]

JS411 Thank you for your help. I have inserted formulas into my spreadsheet and all is working fine. I had been trying to teach myself for weeks how to do it and found this forum. Great forum, I will use it again, if I get stuck as there is other things I would like to add to my spreadsheet later.
Thanks again.

 

[sdezy]

Hi
JS411 was a great help. I have my spreadsheet up and running. I have learnt about pivot tables, added two, even a consolidated pivot table!
I have realised since using the spreadsheet however that with the 'if" formula all of the cells in the "F" column state overdue unless I change the cells in "E" column to completed. In JS411's example the cells do not show overdue when the status of the job is in progress. I have changed the formula to suit my spreadsheet as follows =IF(AND(A14<>"C",G14),"Overdue","") where "C" represents completed in column A. G column has the due dates typed in e.g. 10/04/2011.
Changing the due date does not change status of 'overdue" Have I got something wrong with my formula? Any help would be appreciated.
Sdezy

 

[Jerry Sullivan]

Hi Sdezy,
I had an error in copying the Column F formulas for my previous post. The formula for F7 should read:

=IF(AND(E7<>"Completed",B7< TODAY()),"Overdue","")

Nice to hear that you are expanding your knowledge of Excel to include Pivot Tables and other tools.

 

[sdezy]

Thanks jerry
Corrected the formula and all works beautifully now. Thanks again
for your help.
Steve.