I had the same solution you did. It was a pain in the neck to set up, but got the job done.

Cheers

I've been playing around with this for about 4 years and haven't come up with an elegant way either. Because of the "60" carry the only way is to deal with each "segment" (years, days, hours, minutes, seconds) individually. I also break each down to seconds.

For what it's worth following is the code I use to convert from "display: format (Y:DDD:HH:MM:SS) to seconds and back again. It also can deal with formats like 79h,22:41 (79 hours, 22 minutes and 41 seconds)

Don't know if it "looks" any better than yours but it works just fine.

To Use:

A1	0:048:22:43:27

A2	0:002:01:15:08

A4	FmtTime(A1)		4229007

A5	FmtTime(A2)		177308

A6	FmtTime(SUM(A4:A5))	0:050:23:58:35

Public Function FmtTime(vaRTVal As Variant, Optional stRTBase As String = "S", _
                            Optional stRTFmt As String = "0:000:00:00:00") As Variant

'/*-------------------------------------------------------------------------*/
'/* Function to Format Time                                                 */
'/*                                                                         */
'/* Parms:                                                                  */
'/*     vaRTVal = Time T/B Formatted                                        */
'/*         String = Time in Display Format - Returns Time Value as Double  */
'/*         Number = Time as Number - Returns Formatted Time String         */
'/*                                                                         */
'/*     stRTBase = Number of as Double                                      */
'/*         Y = Time in Years                                               */
'/*         D = Time in Days                                                */
'/*         H = Time in Hours                                               */
'/*         M = Time in Minutes                                             */
'/*         S = Time in Seconds                                             */
'/*                                                                         */
'/*     stRTFmt = Formatted Time Format String                              */
'/*                     Value           Returns                             */
'/*         As Years    0:000:00:00:00  Same                                */
'/*         As Days     000:00:00:00    000d,00:00:00                       */
'/*         As Hours    00:00:00        00h,00:00                           */
'/*         As Minutes: 00:00           00m,00                              */
'/*         As Seconds  00              00s                                 */
'/*                                                                         */
'/* Notes:                                                                  */
'/*     Access Module uses Global Format array                              */
'/*         Loaded at first use by FmtTimeLd().                             */
'/*         Procedure FmtTimeClr() resets                                   */
'/*                                                                         */
'/*  Bill Plachy  02/04/2011                                                */
'/*-------------------------------------------------------------------------*/

    '/*---------------------------------------------------------------------*/
    '/*  Declare some Numeric Values - Not Required I just Like it That Way */
    '/*---------------------------------------------------------------------*/
    const inMOne as integer = -1
    const inZero as integer = 0
    const inOne as integer = 1
    const inTwo as integer = 2
    const inThree as integer = 3
    const inFour as integer = 4

    const coTimeSep as string = ":"
    const coCommaC as string = ","
    const coDashC as string = "-"

    Dim dbTotSec As Double
    Dim dbTimeSgn As Double

    Dim tIx As Integer
    Dim bIx As Integer
    Dim CSIx As Integer

    Dim dbFmtTimeCVA(inFour) As Double                          'Format Time Conversion Values Array
    Dim dbRInt As Double

    Dim vaSplitFmt As Variant

    Dim stFmtTime As String
    Dim stSplitFmt() As String

    '/*-----------------------------------------*/
    '/* Declare Run Time Conversion Constants   */
    '/*-----------------------------------------*/
    Const coYrIx As Integer = 0
    Const coDayIx As Integer = 1
    Const coHrIx As Integer = 2
    Const coMinIx As Integer = 3
    Const coSecIx As Integer = 4

    Const coYrToSec As Double = 31536000
    Const coDayToSec As Double = 86400
    Const coHrToSec As Double = 3600
    Const coMinToSec As Double = 60

    '/*-----------------------------------------*/
    '/* FmtTime() Conversion Base/Return Values */
    '/*-----------------------------------------*/
    Const coFTBaseYr As String = "Y"                            'Base/Return Time in Years
    Const coFTBaseDay As String = "D"                           'Base/Return Time in Days
    Const coFTBaseHr As String = "H"                            'Base/Return Time in Hours
    Const coFTBaseMin As String = "M"                           'Base/Return Time in Minutes
    Const coFTBaseSec As String = "S"                           'Base/Return Time in Seconds

    Const coBaseCtl As String = coFTBaseYr & coFTBaseDay & _
                                coFTBaseHr & coFTBaseMin & _
                                coFTBaseSec                     'Base/Return Validation Values

    bIx = InStr(1, coBaseCtl, Left(stRTBase, inOne), vbTextCompare) 'Validate Base Value
    bIx = bIx - inOne                                               'Index less One

    If bIx < inZero Then                                    'Invalid Base Value
        FmtTime = Null                                          'Return Null Value
    Else                                                    'Base is Valid
        dbFmtTimeCVA(inZero) = coYrToSec                        '/*-----------------------*/
        dbFmtTimeCVA(inOne) = coDayToSec                        '/*                       */
        dbFmtTimeCVA(inTwo) = coHrToSec                         '/* Load Conversion Array */
        dbFmtTimeCVA(inThree) = coMinToSec                      '/*                       */
        dbFmtTimeCVA(inFour) = CDbl(1)                          '/*-----------------------*/

        '/*---------------------------------------------*/
        '/* What to Return is Based on What is Passed   */
        '/*---------------------------------------------*/
        Select Case VarType(vaRTVal)                            'Determine Type of Value
            Case vbInteger, vbLong, vbSingle, vbDouble, _
                    vbCurrency, vbDecimal, vbByte               'Run Time is Numeric - Return Formatted String
                If vaRTVal = inZero Then                            'Value is Zero
                    FmtTime = stRTFmt                                   'Return Zero String
                Else                                                'Value GT Zero
                    stSplitFmt = Split(stRTFmt, coTimeSep, _
                            inMOne, vbTextCompare)                      'Split Format String
                    stFmtTime = vbNullString                            'Clear Formatted Return
                    dbTotSec = vaRTVal * dbFmtTimeCVA(bIx)              'Convert to Seconds
                    CSIx = UBound(dbFmtTimeCVA) - UBound(stSplitFmt)    'Conversion Offset

                    If vaRTVal < inZero Then                            'Negative Value
                        dbTotSec = dbTotSec * inMOne                        'Make Positive
                    End If

                    For tIx = CSIx To UBound(dbFmtTimeCVA) Step inOne   'Format all Sections
                        If dbTotSec >= dbFmtTimeCVA(tIx) Then               'Total Remaining => Conversion
                            dbRInt = Fix(dbTotSec / dbFmtTimeCVA(tIx))          'Divide Total Remaining by Conversion
                        Else                                                'Total Remaining < Conversion
                            dbRInt = inZero                                     'Quotient Whole Number is Zero
                        End If

                        stFmtTime = stFmtTime & _
                                Format(dbRInt, stSplitFmt(tIx - CSIx))      'Store Formatted Segment

                        If tIx = CSIx And CSIx > inZero Then                'First Segment & Returning Less Than Full
                            stFmtTime = stFmtTime & _
                                Mid(coBaseCtl, tIx + inOne, inOne) & _
                                    coCommaC                                    'Append Interval
                        Else                                                'Not First or Full
                            stFmtTime = stFmtTime & coTimeSep                   'Append Seperator
                        End If

                        dbTotSec = dbTotSec - (dbRInt * dbFmtTimeCVA(tIx))  'Seconds Left
                    Next tIx

                    If vaRTVal < inZero Then                            'Negative Value
                        FmtTime = coDashC & _
                                Left(stFmtTime, Len(stFmtTime) - 1)         'Append Minus Sign
                    Else                                                'Positive Value
                        FmtTime = Left(stFmtTime, Len(stFmtTime) - 1)       'Return Formatted Value
                    End If
                End If

            Case vbString                                       'Run Time is String - Return Numeric Value
                dbTotSec = inZero                                   'Clear Total Seconds

                If Left(vaRTVal, inOne) = coDashC Then              'If Negative Value
                    stFmtTime = Right(vaRTVal, Len(vaRTVal) - inOne)    'Make Positive
                    dbTimeSgn = inMOne                                  'Store Sign
                Else                                                'Positive Value
                    stFmtTime = vaRTVal                                 'Use As Is
                    dbTimeSgn = inOne                                   'Store Sign
                End If

                stSplitFmt = Split( _
                                Replace(stFmtTime, coCommaC, coTimeSep, inOne, inMOne, vbTextCompare), _
                            coTimeSep, inMOne, vbTextCompare)       'Split Runtime

                CSIx = UBound(dbFmtTimeCVA) - UBound(stSplitFmt)    'Conversion Offset

                For tIx = 0 To UBound(stSplitFmt) Step 1            'Compute Each Segment
                    dbTotSec = dbTotSec + (Val(stSplitFmt(tIx)) * _
                            dbFmtTimeCVA(tIx + CSIx))                   'Total Seconds
                Next tIx

                FmtTime = CDbl(dbTotSec / dbFmtTimeCVA(bIx)) * dbTimeSgn    'Return in Requested Interval

            Case Else                                           'Invalid Time Value
                FmtTime = Null                                      'Return Null
        End Select
    End If

End Function

Of great help for all of us I think , so many thanks for above contributions

Two of the guys over at the MS Office Excel Forum have come up with formula for addition and subtraction of runtime data using the WGC format y:ddd:mm:hh:mm:ss.

It took a few attempts to get completely right but I've run them against some test data and they appear to work correctly. (Testing involved breaking down input data into it's constituent parts, coverting to seconds, adding or subtracting, then converting the result back to the WGC format. This was then compared with results obtained using the formula below and the results were identical.)

Data ( y:ddd:mm:hh:mm:ss) in Cell A5 and Cell B5; Formula in Cell C5:

Addition formula: (courtesy of Richard Buttrey ) =TEXT(LEFT(B5,FIND(":",B5)-1)+LEFT(A5,FIND(":",A5)-1)+IF(MID(A5,3,3)+MID(B5,3,3)>=365,1,0),"0")&":"&TEXT(MOD(MID(B5,3,3)+MID(A5,3,3),365)+IF(RIGHT(A5,8)+RIGHT(B5,8)>1,1,0),"000")&TEXT(MOD(RIGHT(B5,8)+RIGHT(A5,8),1),":hh:mm:ss")

Subtraction formula: (courtesy of Daddylonglegs)
=LEFT(B5,FIND(":",B5)-1)-LEFT(A5,FIND(":",A5)-1)-(RIGHT(A5,12)>RIGHT(B5,12))&":"&TEXT(MOD(MID(B5,FIND(":",B5)+1,3)-MID(A5,FIND(":",A5)+1,3)-(RIGHT(A5,8)>RIGHT(B5,8)),365),"000")&TEXT(MOD(RIGHT(B5,8)-RIGHT(A5,8),1),":hh:mm:ss")

Notes:
1. Copy/Paste the formula from here to an Excel spreadsheet should work ok.
2. Since I haven't, at this point in time, the knowledge to totally understand/verify the formulae, you should inspect the results produced to ensure correctness.
3. Because cells are text format, subtracting a higher value from a lower value will report a negative as in this example:
0:003:01:11:47 - 0:009:05:44:33 = -1:358:19:27:14
4. If the input data is obtained from an external data connection remember that it may have leading or trailing spaces, which may cause unexpected behaviour in the computation. I handle this by using the "=TRIM() " function on the raw data. Alternatively modify the addition and subtraction formulae to enclose each individual cell reference with TRIM()
5. The formulae assume year=365 days (i.e. No Leap Year)
6. To sort a table of Runtime data produced in this way its necessary to sort on Values, Order Z - A, to produce a table in descending order of magnitude (Because the data is formatted Text rather than Numeric.)
7. Following on from (6) there's a problem: The ddd:mm:hh:mm:ss part of the WGC format is fixed length but "y:" is floating. The sort operation in (6) only works where "y:" =< 9. To handle values of "y:" > 9 it is necessary to introduce leading 0's such that all the "y:" data in the table is of uniform length.

I hope the above proves useful to other people. I'm on a very steep learning curve to update my Excel knowledge so comments, criticism, amendments are most welcome.

anhhai

Thanks for the reply.

I agree with your final paragraph

The principles of this idea should be fairly easy to understand with just the basic understand of how excel works. Modify the idea as you see fit

Had your method been available to me when I first started browsing the Forum I would probably have adopted it in toto. I waded through the FAQs but there was nothing relevant to someone like me who just wants to manipulate WGC data to satisfy a curiosity.

Being a newbie I was initially nervous about posting to a Forum predominately occupied by people who've been crunching for years.

As previously noted my Excel knowledge was somewhat dated but its been a worthwhile and enjoyable process learning some new Excel Functions and putting them into practice (even if it is a little like reinventing the wheel).

When it comes down to it, its a matter of personal preference how to handle the data. But if the discussion in this thread helps others in their quest to manipulate WGC data then we've both achieved something positive.