Not Considering

Hi Cathal,

Thanks for raising this idea for improving the All Tasks Report. Our longer term goal for SDL TMS is to migrate to a central reporting framework which will allow reports to be created and customized according to each user's requirements. For now, you could use an Excel User-Defined Function to format the existing duration into something like the one you're suggesting. For example:

'------------------------------
Public Function FormatDuration(duration As String) As String

Dim days As Long
Dim hours As Long
Dim minutes As Long
Dim components() As String

components = Split(duration, ": ")

Select Case UBound(components)
    Case 2
        days = CLng(Split(components(0), " ")(0))
        hours = CLng(Split(components(1), " ")(0))
        minutes = CLng(Split(components(2), " ")(0))
    Case 1
        days = 0
        hours = CLng(Split(components(0), " ")(0))
        minutes = CLng(Split(components(1), " ")(0))
    Case 0
        days = 0
        hours = 0
        minutes = CLng(Split(components(0), " ")(0))
End Select

FormatDuration = Format$(days, "0000") & " Day(s): " & Format$(hours, "00") & " Hour(s): " & Format$(minutes, "00") & " Minute(s)"

End Function
'------------------------------

 

Is that something that could work for you? Excel 2016 (Windows) 32 bit

  F G
2 12 Min(s) 0000 Day(s): 00 Hour(s): 12 Minute(s)
3 7 Hour(s): 51 Min(s) 0000 Day(s): 07 Hour(s): 51 Minute(s)
4 40 Day(s): 16 Hour(s): 11 Min(s) 0040 Day(s): 16 Hour(s): 11 Minute(s)
5 56 Day(s): 7 Hour(s): 53 Min(s) 0056 Day(s): 07 Hour(s): 53 Minute(s)
6 325 Day(s): 23 Hour(s): 11 Min(s) 0325 Day(s): 23 Hour(s): 11 Minute(s)

All-Jobs-Status_Report_04-07-20



Worksheet Formulas
Cell Formula
G2 =FormatDuration(F2)

David

Default All Tasks Report Enhancement

Currently the all tasks/all job default report returns the time at workflow stage in a format which cannot actually be sorted into anything useful.

e.g

It might show

1 day (s): 23 hours

4 Minutes 

3 Hours.

 

If these are pulled into excel then they cannot be sorted without serious editing to change them all to the same format.

 

Ideally the report should show time at stage in the same format every time

XXXX Day(s) : YY Hour(s) : ZZ Minute(s)

e.g. for the examples given, these would be displayed as below which will always sort correctly to show true time at stage and make the report useful.

 

0001 Day(s) : 00 Hour(s) : 00 Minute(s)

0000 Day(s) : 00 Hour(s) : 04 Minute(s)

0000 Day(s) : 03 Hour(s) : 00 Minute(s)

 

Note : 1000+ days at a stage is not as unusual as it might seem.

Cathal Maguire