How to calculate days since open?
Greetings,
I have a list in Sharepoint that tracks work requests. I would like to display the number of days since the request was opened. Obviously it would stop counting if the item was closed, thereby showing how many days it took to complate. Alternatively,
in "Days Open" it could read, "request now closed" and then another column would display "Days to complete" or something along that line. I currently have a "date open" "date closed" column.
I'm a beginner, and appreciate anyone's help!!
Thanks!
November 14th, 2011 4:47pm
Hi,
you can create in you're list a new Calculated value, named "BEGIN CASE" that can be filled by today ( since request is opened ) !
When people click on "request close NOW", You can create in you're liste a new Calculated value that can be filled by today - BEGIN CASE. and the value will be shown on the second calculated value..
Example:
minutes and seconds must not exceed 60.
Column1
Column2
Formula
Description (possible result)
06/09/2007 10:35 AM
06/09/2007 3:30 PM
=TEXT([Column2]-[Column1],"h")
Hours between two times (4)
06/09/2007 10:35 AM
06/09/2007 3:30 PM
=TEXT([Column2]-[Column1],"h:mm")
Hours and minutes between two times (4:55)
06/09/2007 10:35 AM
06/09/2007 3:30 PM
=TEXT([Column2]-[Column1],"h:mm:ss")
Hours, minutes, and seconds between two times (4:55:00)
More info on official KB from Microsoft:
http://msdn.microsoft.com/en-us/library/bb862071.aspxMicrosoft Certified Technology Specialist: SharePoint 2010, Configuring
Microsoft Certified Personal
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 5:34pm
Thank you,
The problem I'm running into is that the calculated value filled with [today] isn't dynamic, so it won't age correctly if the item isn't manually modified. Unless I'm doing something incorrectly.
November 14th, 2011 5:53pm
Try this code from
http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/
Had the same Issiue as you:
http://social.technet.microsoft.com/Forums/en-SG/sharepointadmin/thread/ac1a6055-1880-4e0e-93a1-e0f5e85653cc
Date
Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”Microsoft Certified Technology Specialist: SharePoint 2010, Configuring
Microsoft Certified Personal
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2011 3:20am
Try this code from
http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/
Had the same Issiue as you:
http://social.technet.microsoft.com/Forums/en-SG/sharepointadmin/thread/ac1a6055-1880-4e0e-93a1-e0f5e85653cc
Date
Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”Microsoft Certified Technology Specialist: SharePoint 2010, Configuring
Microsoft Certified Personal
November 15th, 2011 11:14am