如何用Excel公式或VBA自动将时长字符串转换为[h]:mm:ss格式

Hey there! Let's tackle this duration conversion problem in Excel—super common when dealing with raw time strings like the ones you mentioned. I'll walk you through two solid approaches: a formula-based solution (no coding needed) and a VBA macro for batch processing if you've got tons of entries.

This method extracts numeric values for days, hours, minutes, and seconds from your string, converts them to a consistent time unit, then sums them up. We'll use FILTERXML (works in Excel 2013+) for clean extraction, which handles cases where some units are missing (like your example without hours).

Step-by-Step Formula #

1. Paste this formula into the cell next to your duration string (replace A1 with your target cell):

=SUM(
    IFERROR(VALUE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," days","</s><s>")," hrs","</s><s>")," mins","</s><s>")," secs","</s></t>","//s[1]"))*24,0),
    IFERROR(VALUE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," days","</s><s>")," hrs","</s><s>")," mins","</s><s>")," secs","</s></t>","//s[2]")),0),
    IFERROR(VALUE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," days","</s><s>")," hrs","</s><s>")," mins","</s><s>")," secs","</s></t>","//s[3]"))/60,0),
    IFERROR(VALUE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," days","</s><s>")," hrs","</s><s>")," mins","</s><s>")," secs","</s></t>","//s[4]"))/3600,0)
)

2. Format the cell correctly: Right-click the cell → Format Cells → Custom → Enter [h]:mm:ss in the type field. The square brackets ensure Excel displays hours over 24 instead of rolling over to days.

For Older Excel Versions (Pre-2013) #

If FILTERXML isn't available, use this nested extraction method:

= (IFERROR(VALUE(LEFT(A1,SEARCH(" days",A1)-1)),0)*24) + 
  IFERROR(VALUE(MID(A1,IFERROR(SEARCH(" days",A1)+6,1),SEARCH(" hrs",A1)-IFERROR(SEARCH(" days",A1)+6,1))),0) + 
  IFERROR(VALUE(MID(A1,IFERROR(SEARCH(" hrs",A1)+4,IFERROR(SEARCH(" days",A1)+6,1)),SEARCH(" mins",A1)-IFERROR(SEARCH(" hrs",A1)+4,IFERROR(SEARCH(" days",A1)+6,1))),0)/60 + 
  IFERROR(VALUE(MID(A1,IFERROR(SEARCH(" mins",A1)+5,IFERROR(SEARCH(" hrs",A1)+4,IFERROR(SEARCH(" days",A1)+6,1))),SEARCH(" secs",A1)-IFERROR(SEARCH(" mins",A1)+5,IFERROR(SEARCH(" hrs",A1)+4,IFERROR(SEARCH(" days",A1)+6,1)))),0)/3600

Again, set the cell format to [h]:mm:ss after entering the formula.

If you have a huge list of durations, a custom VBA function will save you time. It lets you convert strings with a simple formula, and handles missing units automatically.

Step-by-Step Setup #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste this code into the module:

Function ConvertDuration(durString As String) As Double
    Dim days As Double, hrs As Double, mins As Double, secs As Double
    Dim parts() As String
    Dim i As Integer
    
    ' Split the duration string into value-unit pairs
    parts = Split(durString, " ")
    
    ' Loop through each pair to extract values
    For i = LBound(parts) To UBound(parts) Step 2
        Select Case UCase(parts(i + 1))
            Case "DAYS"
                days = CDbl(parts(i))
            Case "HRS"
                hrs = CDbl(parts(i))
            Case "MINS"
                mins = CDbl(parts(i))
            Case "SECS"
                secs = CDbl(parts(i))
        End Select
    Next i
    
    ' Convert all to days (Excel stores time as fractions of a day)
    ConvertDuration = days + (hrs / 24) + (mins / 1440) + (secs / 86400)
End Function

4. Close the VBA Editor and return to Excel.
5. Use the function in any cell: =ConvertDuration(A1) (replace A1 with your duration cell).
6. Set the cell format to [h]:mm:ss as before.

Pro Tips #

• Both methods handle missing units (like your example without hours) seamlessly—they'll just treat missing values as 0.
• If your strings use different abbreviations (e.g., "hour" instead of "hrs"), adjust the formula/VBA code to match those terms.

内容的提问来源于stack exchange，提问作者Will