Using a count function to work out the length of a jobs cycle

[Mchgh9]

Hi I am trying to calculate the life cycle from one date to another.

Todays date in A1.

The only thing is I want the count to stop when the text in a column changes from a specfic string of "WorkInProgress".

So I have a start/creation date in Column C3.

A payment date in Column E3, which is going to be used to work the duration out.

So if B3 = "WorkInProgress" NETWORKDAYS(Payment app date-Creation Date).

I just can't seem to write the formula in the correct order.

Any help much appreciated.
Thanks

Dave

<TABLE style="WIDTH: 362pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=483 border=0><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" span=2 width=67><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=106 height=34>06/06/2012

</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=115></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=67></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=67></TD><TD class=xl71 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 80pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: silver" width=106 height=34>Diary Sheet Number Desc</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=115>Status</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=64>Creation Date</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=67>Last Modified</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=67>Payment App Date</TD><TD class=xl72 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=64>Days Open</TD></TR><TR style="HEIGHT: 51.75pt; mso-height-source: userset" height=69><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: transparent" width=106 height=69>ENTEW16843</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>WorkInProgress</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>01-Jan-12</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=67>24-May-12</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 50pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=67>11-May-12</TD><TD class=xl73 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>25

</TD></TR></TBODY></TABLE>

 

[earthworm]

=IF(B4="Work in Process",NETWORKDAYS(C4,E4),"")

Sample Attached

https://www.box.com/s/e8649e793ded1f8c811a