http://www.endusersharepoint.com/STP/ Paul Grenier, Lead Moderator en Fri, 03 Apr 2009 19:19:04 +0000 http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5632 Thu, 02 Apr 2009 13:44:37 +0000 Dessie Lunsford 5632@http://www.endusersharepoint.com/STP/ <p>Glad to have helped :)</p> <p>- Dessie </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5618 Wed, 01 Apr 2009 21:16:32 +0000 NancyCentury 5618@http://www.endusersharepoint.com/STP/ <p>I can't thank you enough. But thank you anyway!!! </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5611 Wed, 01 Apr 2009 15:36:17 +0000 Dessie Lunsford 5611@http://www.endusersharepoint.com/STP/ <p>Yeah :) Looks like the DIV's came through ok in the post (I've had problems with them in the past, but it appears that the "code" tag actualy worked for me).</p> <p>- Dessie </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5610 Wed, 01 Apr 2009 15:30:34 +0000 Dessie Lunsford 5610@http://www.endusersharepoint.com/STP/ <p>I believe the "DATEDIF" function will ignore time values so using it will only give you the amount of days difference.</p> <p>Try this (starting over from scratch):</p> <p>Column 1: Start Date: type = Date and Time</p> <p>Column 2: End Date: type = Date and Time</p> <p>Column 3: Target Duration: type = Single line of text (text entered as Days:Hours:Minutes)</p> <p>Column 4: Actual Duration: type = Calculated<br /> Formula:<br /> =INT([End Date]-[Start Date])&#38;"days "&#38;INT(MOD([End Date]-[Start Date],1)*24)&#38;"hrs "&#38;INT(MOD(MOD([End Date]-[Start Date],1)*24,1)*61)&#38;"mins"</p> <p>Column 5: Time Taken: type = Calculated<br /> Formula:<br /> =TEXT(VALUE(INT([End Date]-[Start Date])&#38;":"&#38;INT(MOD([End Date]-[Start Date],1)*24)&#38;":"&#38;INT(MOD(MOD([End Date]-[Start Date],1)*24,1)*61)),"hh:mm:ss")</p> <p>Column 6: Traffic Light: type = Calculated<br /> Formula:<br /> <code>="&lt;DIV style='font-weight:bold; font-size:24px; color:"&#38;IF([Time Taken]-[Target Duration]=0,"green",IF([Time Taken]-[Target Duration]&gt;0,"red","green"))&#38;";'&gt;•&lt;/DIV&gt;"</code></p> <p>Column 7: Over-Under: type = Calculated<br /> Formula:<br /> =IF([Time Taken]-[Target Duration]=0,"On-Time",IF([Time Taken]-[Target Duration]&gt;0,"Over","Under"))</p> <p>Last step: add in the Javascript from Christophe's articles to a CEWP (make sure and drag it below the list) to convert the DIV tags into real html and display the red/green indicators.</p> <p>After doing this, add in a new item:</p> <p>Title: Task A<br /> Start Date: 4/13/2009 8:00 AM<br /> End Date: 4/16/2009 2:30 PM<br /> Target Duration: 3:04:00</p> <p>This will display as:</p> <p>Title: Task A<br /> Start Date: 4/13/2009 8:00 AM<br /> End Date: 4/16/2009 2:30 PM<br /> Target Duration: 3:04:00<br /> Actual Duration: 3days 6hrs 30mins<br /> Time Taken: 03:06:30<br /> Traffic Light: red<br /> Over-Under: Over</p> <p>Make a few edits to change the End Date to equal the target duration and then less than the target duration and you should see the traffic light change to green when its on-time (equal to target duration) and under (less than target duration), as well as the over-under text change accordingly.</p> <p>You may notice that on occasion, the "Time Taken" value may display as 29 or 59 instead of 30 or 00. This is because of how we're counting the starting and ending minutes (SharePoint and its logic). You'll probably only see it if the time is on the half hour, but its just how SharePoint performs the calculation, so dont worry too much about it.</p> <p>You could probably just use either the "Actual Duration" column or "Time Taken" column since they both represent the same information (just differently), but both could be nice for different views.</p> <p>Does this get you closer to what "They" want? </p> <p>- Dessie</p> <p>(Note - I may need to edit this if the "DIV" stuff doesn't display properly - happens on occasion) </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5601 Wed, 01 Apr 2009 13:47:34 +0000 NancyCentury 5601@http://www.endusersharepoint.com/STP/ <p>I (sort of) did it! </p> <p>I used: =IF([End time]&lt;[Due Date],"YES","NO")</p> <p>It doesn't give me the exact amount of time between the two dates, but it tells me whether the step was completed on time or now.</p> <p>Will this do that?<br /> =DATEDIF([Due date],[End time],"hh:mm:ss") </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5600 Wed, 01 Apr 2009 13:34:44 +0000 NancyCentury 5600@http://www.endusersharepoint.com/STP/ <p>I cannot thank you enough- and I will definitely read that material. You get a gift basket for sure!</p> <p>That being said... it's so true that the more you give them, the more they want.</p> <p>I also <strong>now</strong> need to compare the actual completion date (End time) vs the Due date, because not only do they want to track the <em>duration</em> of certain steps, they want to track whether other specific steps were completed by the due date- tracking timeliness for some steps, length of time for others. </p> <p>I have Due Date and End Time fields (both of which are 'true' date/time fields, NOT the hh:mm:ss text-type). I need to calculate how much under/over in relation to the Due Date the End time turned out to be. </p> <p>This calculated value I would call "Completion result". (Then I can see your original solution of how to display the over/under adaptable to this scenario?)</p> <p><strong>However,</strong> I can't get my "Completion result" to show me that I want. I am envisioning a negative value if the End Time is greater than the Due date and a positive value for the opposite.</p> <p>Since the 2 fields in the formula are date/time, how can I get this type of result? </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5592 Wed, 01 Apr 2009 12:52:50 +0000 Dessie Lunsford 5592@http://www.endusersharepoint.com/STP/ <p>Take a look at the series of articles on "Using calculated columns to write HTML" by Christophe at "PathToSharePoint.com".</p> <p><a href="http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/" rel="nofollow">http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/</a><br /> <a href="http://pathtosharepoint.wordpress.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/" rel="nofollow">http://pathtosharepoint.wordpress.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/</a><br /> <a href="http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/" rel="nofollow">http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/</a></p> <p>Mark also did a great workshop with Dashboards awhile back detailing how to implement this approach (maybe again sometime in the near future?), but what Christophe details should get you going.</p> <p>You're definately on the right track with your "Contains" idea for how to determine which image to display. The way I'd approach it is:</p> <p>IF(ISERROR(FIND("Over",[Duration Comparison])),"green","red")</p> <p>This looks to see if it can find the text "Over" in the returned value of the "Duration Comparison" column. Since the "FIND" function only returns the index position of the text if it finds it, and returns a "#VALUE" error if it doesn't find the text, we use the "ISERROR" function to determine if it did find it. If it did successfully find the text, that would mean there was no error, so our "IF" returns a "FALSE" value that displays a "red". If it does return an error (the column did not contain the text), the "IF" returns "TRUE", so it displays a "green".</p> <p>It's kind've a backward-logic process since we're checking to see if it returns an error, but it does get the end result.</p> <p>In Christophe's articles he uses a "CHOOSE" function to determine which value (green or red) to use, so just replace the entire "CHOOSE" with the above "IF" check (retaining the rest of his formula with the divs) and you should get what you need.</p> <p>I didnt want to repost his steps and information here since he does such a great job of explaining things, but use what I've detailed above in addition to his steps and you should be able to get the "Green / Red" display as required.</p> <p>---------------------------</p> <p>For the other question about times greater than 24 hrs, this approach wont really work since we're only using the "hh:mm:ss" convention to get the value (this will only work for a time period within a single day). I'm sure there's a way to accomplish this by switching over to a full datetime value instead, but I'll have to play with it some to see how to do it.</p> <p>- Dessie </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5570 Wed, 01 Apr 2009 08:14:46 +0000 NancyCentury 5570@http://www.endusersharepoint.com/STP/ <p>I also have one observation/question... the Target Duration value (apparently) cannot break the 24-hours threshold? </p> <p>When I gave an item a Target Duration of 24:00:00 or above (to indicate something taking 2 days or three days etc.), the Duration Comparison returns an error. </p> <p>Do I need to make a modification? It very well could be that some of the process steps will need to allot longer periods of time (maybe up to a week). </p> <p>Thank you! </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5569 Wed, 01 Apr 2009 08:01:50 +0000 NancyCentury 5569@http://www.endusersharepoint.com/STP/ <p>So that means ( I assume), a calculated column which will involve something like IF [Duration comparison] contains "Over", html to create a red dot, else html to create a green dot. But that's as far as I got. </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5568 Wed, 01 Apr 2009 07:40:44 +0000 NancyCentury 5568@http://www.endusersharepoint.com/STP/ <p>Of course, now you know what they want... a column with a green dot = under, red dot = over. :-) </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5559 Tue, 31 Mar 2009 20:20:47 +0000 EndUserSharePoint 5559@http://www.endusersharepoint.com/STP/ <p>Dessie's back.... :-) </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5557 Tue, 31 Mar 2009 18:44:40 +0000 NancyCentury 5557@http://www.endusersharepoint.com/STP/ <p>That. is. so. awesome. THANK YOU! </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5545 Tue, 31 Mar 2009 15:10:45 +0000 Dessie Lunsford 5545@http://www.endusersharepoint.com/STP/ <p>Ok, starting to make sense now.</p> <p>Everything sounds good so far...the "Duration" column should be returning a value in the "h:mm:ss" format (which it sounds like it is).</p> <p>So, to add in a "Target Duration", you can just create a new "Single line of text" column and literally enter in the values in the same "h:mm:ss" format (i.e. 2.5 hrs would be 2:30:00). Do this for each step you want to track. (Q: are there any issues with manually entering in this data?)</p> <p>For the "Duration Comparison" column, just use a Calculated column with a return type of "Single line of text" and a formula something like:</p> <p>=IF(TEXT([Actual Duration],"HH:MM:SS")&lt;TEXT([Target Duration],"HH:MM:SS"),TEXT([Target Duration]-[Actual Duration],"HH:MM:SS")&#38;" Under",TEXT([Actual Duration]-[Target Duration],"HH:MM:SS")&#38;" Over")</p> <p>This formula would be read as: "If the text of the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format is less than the text of the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, subtract the 'Actual Duration' column from the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Under'. If not, subtract the 'Target Duration' column from the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Over'."</p> <p>This would display (using example data):</p> <p>Start Time: 9:00:00<br /> End Time: 9:45:00<br /> Actual Duration: 00:45:00<br /> Target Duration: 1:30:00<br /> Duration Comparison: 00:45:00 Under</p> <p>Getting a bit trickier, you could use an alternate formula of:</p> <p>=IF(TEXT([Actual Duration],"HH:MM:SS")&lt;TEXT([Target Duration],"HH:MM:SS"),HOUR([Target Duration]-[Actual Duration])&#38;" hrs "&#38;MINUTE([Target Duration]-[Actual Duration])&#38;" min "&#38;SECOND([Target Duration]-[Actual Duration])&#38;" sec Under",HOUR([Actual Duration]-[Target Duration])&#38;" hrs "&#38;MINUTE([Actual Duration]-[Target Duration])&#38;" min "&#38;SECOND([Actual Duration]-[Target Duration])&#38;" sec Over")</p> <p>This would display it as:</p> <p>Start Time: 9:00:00<br /> End Time: 9:45:00<br /> Actual Duration: 00:45:00<br /> Target Duration: 1:30:00<br /> Duration Comparison: 0 hrs 45 min 0 sec Under</p> <p>This one parses out the individual hours/minutes/seconds into a more "textual" display rather than the "hh:mm:ss" style display, but either version would work.</p> <p>The custom view portion should be pretty straight-forward, but does this help with the rest?</p> <p>- Dessie </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5542 Tue, 31 Mar 2009 13:48:54 +0000 NancyCentury 5542@http://www.endusersharepoint.com/STP/ <p>My custom CT is called Process Step. (The parent CT is Issue.) The purpose of the list is to allow loading of a series of steps (the same set for each month) and track the time it takes to complete each step, focusing on those identified as "key steps."</p> <p>As I said, I added "start time," "end time" and "duration" to my content type. Start time is a date/time field which populates off a WF that uses "set list item" action to drop the date/time corresponding to "modified" which occurs when the item status changes from "not started" to "in progress." The same thing happens when status changes from "in progress" to "completed" - another WF updates "end time" to copy over whatever was the "modified" date/time matching that change. </p> <p>"Duration" is a calculated column derived from end time minus start time, single line of text type, using this formula:<br /> =TEXT([End time]-[Start time],"h:mm:ss")</p> <p>The result of the calculation appears in the hours:minutes:seconds format</p> <p>I want to provide a way to measure the duration against a target duration; therefore, the values I enter for each step as my target duration must also be in the hours:minutes:seconds format- correct? </p> <p>Assuming that can be created, I then want one more calculated column )"duration comparison") which will compare the actual duration with the target duration. I am fuzzy on how to display this, however... I'm not sure what the best comparison format would be (a difference, a ratio, not sure). So I don't know what type of field to use. I think it will depend on how the target duration field is formatted.</p> <p>Finally, I will create a view to display only the "key steps" and set up a KPI that lets me see the key steps' duration comparison values. </p> <p>Is that the info you need? </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5538 Tue, 31 Mar 2009 11:53:04 +0000 Dessie Lunsford 5538@http://www.endusersharepoint.com/STP/ <p>Nancy,<br /> Can you detail the schema of your list so I can duplicate it? How did you set up each column? What are their types, etc?</p> <p>Since there isn't specifically a "Time-only" column type, this may get tricky to get it to work as you want, but we may be able to come up with something.</p> <p>- Dessie </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5519 Mon, 30 Mar 2009 16:27:07 +0000 EndUserSharePoint 5519@http://www.endusersharepoint.com/STP/ <p>Nancy - If Dessie come up for air, I'm sure he'll take a look at this one. -- Mark </p> http://www.endusersharepoint.com/STP/topic/how-to-format-a-time-value-in-a-list-column#post-5517 Mon, 30 Mar 2009 16:19:48 +0000 NancyCentury 5517@http://www.endusersharepoint.com/STP/ <p>I have a list of tasks whose fields include start time, end time and a calculated column called Actual Duration which derives its value from end time - start time. All these columns use the hours:minutes:seconds format.</p> <p>I want to compare the actual duration to a target duration and use that comparison value in a KPI list. This would require another column I could call "Actual vs Target" or something like that.</p> <p>So if Task A has a target duration of 1 hour 30 minutes, and an actual duration of 45 minutes, (end time - start time = 45 minutes), the comparison would show a 45-minute difference ( 00:45:00); conversely, if actual duration was 2 hours, the value would be -30 minutes (-00:30:00). </p> <p>When I add a new list item and want to key in a Target Duration, how can I get that value to be in the hours:minutes format? It's not date/time; it's not text. I think it's some form of calculated column but I don't know how to get there. </p> <p>Thank you! </p>