Count of days open on a job until a different column reads "complete" then keep the count

M

Mchgh9

New Member
Joined
Feb 2, 2012
Messages
39
Hi everyone,

I have become stuck on working out a formula to calculate the length of a job counting backwards from todays date to the start, while the job has a status of "WorkInProgress".

The other potential status' are "complete",
"CreatedInError" and "cancelled". If it is created in error it can be discarded.

My problem lies in that I have been able to count the life of the job quite simply, however when the status changes to complete I don't know how to both stop and store the count in a seperate field.

In order to count I have "=DAYS360(W3,A$1)"

Where A1 is "=TODAY()"



<TABLE style="WIDTH: 590pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=786 border=0><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6400" width=175><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=167 height=17>01/06/2012</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=106></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=93></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 110pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=146></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 131pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=175></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=99></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Diary Sheet Number Desc</TD><TD class=xl75 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Status</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Creation Date</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Scope 2 Last Mod Date</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Scope 2 Payment App Date</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Days Open</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 125pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=167 height=40>ENTEW16843</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=106>WorkInProgress</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 70pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=93>19-Apr-12</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 110pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=146>24-May-12</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 131pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=175>11-May-12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>20</TD></TR></TBODY></TABLE>


Above is my sheet.

W3 is "Scope 2 Payment App Date"


So to summarise I would like to be able to keep a count of the length of a job from "Scope 2 Payment App Date" until the status changes to either Complete, CreatedInError, Cancelled.

I found to hard to explain this, so feel free to ask questions if you need more clarity.

Thanks for your help.

Dave
 
Sort by date Sort by votes
Can anyone help with this please?

Mchgh9 said:
Hi everyone,

I have become stuck on working out a formula to calculate the length of a job counting backwards from todays date to the start, while the job has a status of "WorkInProgress".

The other potential status' are "complete",
"CreatedInError" and "cancelled". If it is created in error it can be discarded.

My problem lies in that I have been able to count the life of the job quite simply, however when the status changes to complete I don't know how to both stop and store the count in a seperate field.

In order to count I have "=DAYS360(W3,A$1)"

Where A1 is "=TODAY()"



<TABLE style="WIDTH: 590pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=786 border=0><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6400" width=175><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=167 height=17>01/06/2012</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=106></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=93></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 110pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=146></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 131pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=175></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=99></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Diary Sheet Number Desc</TD><TD class=xl75 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Status</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Creation Date</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Scope 2 Last Mod Date</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Scope 2 Payment App Date</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Days Open</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 125pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=167 height=40>ENTEW16843</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=106>WorkInProgress</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 70pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=93>19-Apr-12</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 110pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=146>24-May-12</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 131pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=175>11-May-12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>20</TD></TR></TBODY></TABLE>


Above is my sheet.

W3 is "Scope 2 Payment App Date"


So to summarise I would like to be able to keep a count of the length of a job from "Scope 2 Payment App Date" until the status changes to either Complete, CreatedInError, Cancelled.

I found to hard to explain this, so feel free to ask questions if you need more clarity.

Thanks for your help.

Dave
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
914
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
R
  • Question Question
Random
Replies
1
Views
597
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top