如何用Excel公式将Jira耗时格式转换为纯分钟数？

No problem at all—let's build a reliable Excel formula that handles all variations of Jira's time format (days, hours, minutes, and any combination of them) since you can't regenerate the original sheet.

The Formula #

Here's a robust formula that works for your example cases and edge scenarios (like entries missing days/hours, extra spaces, etc.):

=IFERROR(
    (IFERROR(VALUE(MID(A1, 1, SEARCH("d", A1)-1))*1440, 0) +
    IFERROR(VALUE(MID(A1, IFERROR(SEARCH("d", A1)+1, 1), IFERROR(SEARCH("h", A1)-IFERROR(SEARCH("d", A1), 0)-1, LEN(A1))))*60, 0) +
    IFERROR(VALUE(MID(A1, IFERROR(MAX(SEARCH("d", A1), SEARCH("h", A1))+1, 1), LEN(A1)-IFERROR(MAX(SEARCH("d", A1), SEARCH("h", A1)), 0))), 0)
, 0)

(Note: Replace A1 with the cell containing your Jira time value. Drag this formula down the column to apply it to all rows.)

How It Works #

Let's break down the formula to see why it handles every case:

• Days to Minutes: The first segment checks for the "d" identifier, extracts the number before it, and multiplies by 1440 (1 day = 1440 minutes). If there's no "d", it returns 0.
• Hours to Minutes: The second segment looks for the "h" identifier. If a "d" exists, it starts extracting right after the "d"; otherwise, it starts from the cell's beginning. The number is multiplied by 60 (1 hour = 60 minutes), or returns 0 if no "h" is present.
• Minutes: The final segment grabs the number after the last of either "d" or "h" (or the whole cell if neither exists) to use as the minute count.
• Error Handling: Every segment uses IFERROR to avoid #VALUE! errors when a time unit is missing, and the entire formula wraps up with IFERROR to return 0 for empty cells.

Example Results #

Using your sample data, you'll get exactly the output you need:

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