Stump the Panel

Paul Grenier, Lead Moderator

Stump the Panel » Site Managers and Site Collection Managers

1. 

   NancyCentury
   Member

   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.

   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.

   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).

   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.

   Thank you!

   Posted 2 days ago #
2. 

   EndUserSharePoint
   Key Master

   Nancy - If Dessie come up for air, I'm sure he'll take a look at this one. -- Mark

   Posted 2 days ago #
3. 

   Dessie Lunsford
   Moderator

   Nancy,
   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?

   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.

   - Dessie

   Posted 2 days ago #
4. 

   NancyCentury
   Member

   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."

   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.

   "Duration" is a calculated column derived from end time minus start time, single line of text type, using this formula:
   =TEXT([End time]-[Start time],"h:mm:ss")

   The result of the calculation appears in the hours:minutes:seconds format

   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?

   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.

   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.

   Is that the info you need?

   Posted 2 days ago #
5. 

   Dessie Lunsford
   Moderator

   Ok, starting to make sense now.

   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).

   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?)

   For the "Duration Comparison" column, just use a Calculated column with a return type of "Single line of text" and a formula something like:

   =IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),TEXT([Target Duration]-[Actual Duration],"HH:MM:SS")&" Under",TEXT([Actual Duration]-[Target Duration],"HH:MM:SS")&" Over")

   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'."

   This would display (using example data):

   Start Time: 9:00:00
   End Time: 9:45:00
   Actual Duration: 00:45:00
   Target Duration: 1:30:00
   Duration Comparison: 00:45:00 Under

   Getting a bit trickier, you could use an alternate formula of:

   =IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),HOUR([Target Duration]-[Actual Duration])&" hrs "&MINUTE([Target Duration]-[Actual Duration])&" min "&SECOND([Target Duration]-[Actual Duration])&" sec Under",HOUR([Actual Duration]-[Target Duration])&" hrs "&MINUTE([Actual Duration]-[Target Duration])&" min "&SECOND([Actual Duration]-[Target Duration])&" sec Over")

   This would display it as:

   Start Time: 9:00:00
   End Time: 9:45:00
   Actual Duration: 00:45:00
   Target Duration: 1:30:00
   Duration Comparison: 0 hrs 45 min 0 sec Under

   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.

   The custom view portion should be pretty straight-forward, but does this help with the rest?

   - Dessie

   Posted 1 day ago #
6. 

   NancyCentury
   Member

   That. is. so. awesome. THANK YOU!

   Posted 1 day ago #
7. 

   EndUserSharePoint
   Key Master

   Dessie's back.... :-)

   Posted 1 day ago #
8. 

   NancyCentury
   Member

   Of course, now you know what they want... a column with a green dot = under, red dot = over. :-)

   Posted 1 day ago #
9. 

   NancyCentury
   Member

   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.

   Posted 1 day ago #
10. 

    NancyCentury
    Member

    I also have one observation/question... the Target Duration value (apparently) cannot break the 24-hours threshold?

    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.

    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).

    Thank you!

    Posted 1 day ago #
11. 

    Dessie Lunsford
    Moderator

    Take a look at the series of articles on "Using calculated columns to write HTML" by Christophe at "PathToSharePoint.com".

    http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/
    http://pathtosharepoint.wordpress.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/
    http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/

    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.

    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:

    IF(ISERROR(FIND("Over",[Duration Comparison])),"green","red")

    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".

    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.

    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.

    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.

    ---------------------------

    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.

    - Dessie

    Posted 1 day ago #
12. 

    NancyCentury
    Member

    I cannot thank you enough- and I will definitely read that material. You get a gift basket for sure!

    That being said... it's so true that the more you give them, the more they want.

    I also now need to compare the actual completion date (End time) vs the Due date, because not only do they want to track the duration 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.

    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.

    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?)

    However, 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.

    Since the 2 fields in the formula are date/time, how can I get this type of result?

    Posted 1 day ago #
13. 

    NancyCentury
    Member

    I (sort of) did it!

    I used: =IF([End time]<[Due Date],"YES","NO")

    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.

    Will this do that?
    =DATEDIF([Due date],[End time],"hh:mm:ss")

    Posted 1 day ago #
14. 

    Dessie Lunsford
    Moderator

    I believe the "DATEDIF" function will ignore time values so using it will only give you the amount of days difference.

    Try this (starting over from scratch):

    Column 1: Start Date: type = Date and Time

    Column 2: End Date: type = Date and Time

    Column 3: Target Duration: type = Single line of text (text entered as Days:Hours:Minutes)

    Column 4: Actual Duration: type = Calculated
    Formula:
    =INT([End Date]-[Start Date])&"days "&INT(MOD([End Date]-[Start Date],1)*24)&"hrs "&INT(MOD(MOD([End Date]-[Start Date],1)*24,1)*61)&"mins"

    Column 5: Time Taken: type = Calculated
    Formula:
    =TEXT(VALUE(INT([End Date]-[Start Date])&":"&INT(MOD([End Date]-[Start Date],1)*24)&":"&INT(MOD(MOD([End Date]-[Start Date],1)*24,1)*61)),"hh:mm:ss")

    Column 6: Traffic Light: type = Calculated
    Formula:
    ="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Time Taken]-[Target Duration]=0,"green",IF([Time Taken]-[Target Duration]>0,"red","green"))&";'>•</DIV>"

    Column 7: Over-Under: type = Calculated
    Formula:
    =IF([Time Taken]-[Target Duration]=0,"On-Time",IF([Time Taken]-[Target Duration]>0,"Over","Under"))

    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.

    After doing this, add in a new item:

    Title: Task A
    Start Date: 4/13/2009 8:00 AM
    End Date: 4/16/2009 2:30 PM
    Target Duration: 3:04:00

    This will display as:

    Title: Task A
    Start Date: 4/13/2009 8:00 AM
    End Date: 4/16/2009 2:30 PM
    Target Duration: 3:04:00
    Actual Duration: 3days 6hrs 30mins
    Time Taken: 03:06:30
    Traffic Light: red
    Over-Under: Over

    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.

    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.

    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.

    Does this get you closer to what "They" want?

    - Dessie

    (Note - I may need to edit this if the "DIV" stuff doesn't display properly - happens on occasion)

    Posted 22 hours ago #
15. 

    Dessie Lunsford
    Moderator

    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).

    - Dessie

    Posted 22 hours ago #
16. 

    NancyCentury
    Member

    I can't thank you enough. But thank you anyway!!!

    Posted 16 hours ago #
17. 

    Dessie Lunsford
    Moderator

    Glad to have helped :)

    - Dessie

    Posted 23 minutes ago #

RSS feed for this topic

Reply

You must log in to post.