How to format and calculate task days in process?

[laurieny]

For weekly staff meetings I need to provide updates via a spreadsheet of tasks to be done by various people. The table includes the Date Assigned (F2 for instance and possibly blank when not yet determined whom to assign the task), the Date Completed (G2 and often is blank) and then calculate in H2 the number of WORKDAY(s) (ie no weekends or holidays) that the task has been in process from TODAY(). When the task has yet to be assigned, then H2 (Time in Process)should be blank. If the task is assigned and TODAY() is at least one day past the date assigned, then H2 should be the number of days passed. When the number of days in H2 hits or exceeds 10 days, then the cell should be highlighted red. When the task is assigned but the date is greater than today (we know that something can't be started until next week for instance, but it gets assigned to that date in our staff meeting), then H2 should be blank. When the task if completed (ie G2 now has a date) then H2 should just be the number of days it took (but not red even if it took more than 10 days to complete).

I prefer not to embarass myself with the various formulas I have tried in order to accomplish this. If there is a way to do this without outside scripting, I would be most appreciative.

Thanks in advance for any assistance.
-Laurie

 

[VoG]

Hello. Take a look at NETWORKDAYS in the Analysis ToolPak (tools > Add-Ins)

 

[laurieny]

Yes, that helps me get the number of days, but it is the conditionals that I can't get right. So for instance if I do:

=IF(G2,"",ABS(NETWORKDAYS(TODAY(),F2)))

Then that works fine for when a date is assigned, and then I try a more elaborate IF to handle when a task is completed(ie G2 is not blank) then NETWORKDAYS(F2, G2) and that isn't working. And that still doesn't handle the case where no date exists in either F2 or G2 (ie the task has not yet been assigned) and therefore I want H2 to be blank. And of course then I just apply conditional formatting to the H2 column when the number is greater than 10.

Does that explain it better?

 

[VoG]

Maybe this is a start - you might need to build in more conditions

Excel Workbook
	F	G	H
1	Assigned	Completed	Over
2	01/03/2011	05/03/2011
3
4	01/03/2011		19
5	20/03/2011		5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	1. / Formula is =AND(H2<>"",H2>=10)	Abc

 

[laurieny]

Yep, but I need one more level G2 is not blank (ie the task is done) that then calculates the number of days that took-so H2 is populated.

And I really appreciate your help - thanks!

 

[VoG]

Maybe like this (CF not shown)

Excel Workbook
	F	G	H
1	Assigned	Completed	Over
2	01/03/2011	05/03/2011	4
3
4	01/03/2011		19
5	20/03/2011		5
6	01/03/2011	14/03/2011	13
Sheet7

 

[laurieny]

DUh, I was just making this too hard. That is just what I need. Thank you for your help very much!