[Solved] Converting Time Durations to Excel Usable Format

[JOGEC]

Hello all,

I have tried various things such as left, right, mid, hour, minutes, and seconds functions. I am stuck and have not been able to find any specific information on this problem. I have included a sample dataset below. I would like to convert these numbers into minutes or hours. Does anyone have any ideas on how I could accomplish this? Thanks in advance for your help!

4m 58s

2h 46m 19s

4m 48s

43m 12s

7m 12s

46s

2h 38m 24s

7m 58s

38m 2s

6m 59s

1h 53m 23s

7m 58s

2h 38m 24s

15m

 

[shamimarita]

Hello Jogec,

You can use the following formulas but must maintain a proper time format to get the correct result.

Convert to Total Minutes:
Once the time is correctly formatted, multiply it by 1440:

=IFERROR(TIME(LEFT(A1,FIND("h",A1)-1),MID(A1,FIND("h",A1)+1,FIND("m",A1)-FIND("h",A1)-1),MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1)),
IFERROR(TIME(0,LEFT(A1,FIND("m",A1)-1),MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1)),
IFERROR(TIME(0,0,LEFT(A1,FIND("s",A1)-1)),0)))*1440

Convert to Total Hours:
Similarly, multiply the correctly formatted time by 24 for hours:

=IFERROR(TIME(LEFT(A1,FIND("h",A1)-1),MID(A1,FIND("h",A1)+1,FIND("m",A1)-FIND("h",A1)-1),MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1)),
IFERROR(TIME(0,LEFT(A1,FIND("m",A1)-1),MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1)),
IFERROR(TIME(0,0,LEFT(A1,FIND("s",A1)-1)),0)))*24

Why This Works:

• TIME(h, m, s): Converts hours, minutes, and seconds into a proper time format.
• IFERROR: Handles cases where h, m, or s components are missing.
• Multiplying by 1440 or 24 scales the time value correctly.

 

[JOGEC]

Hello Shamimarita,

Thank you for your response. I tried to use the formula, but it appears to only work when the seconds are in the data cell. I have attached a sample data book to show you how it looks. Am I doing something incorrectly?

 

[shamimarita]

Hello Jogec,

You can use the following formula:
=IF(ISNUMBER(SEARCH("h", A2)), LEFT(A2, SEARCH("h", A2) - 1) * 60, 0) +
IF(ISNUMBER(SEARCH("m", A2)), MID(A2, IF(ISNUMBER(SEARCH("h", A2)), SEARCH("h", A2) + 1, 1), SEARCH("m", A2) - IF(ISNUMBER(SEARCH("h", A2)), SEARCH("h", A2) + 1, 1)), 0) +
IF(ISNUMBER(SEARCH("s", A2)), MID(A2, SEARCH("m", A2) + 1, SEARCH("s", A2) - SEARCH("m", A2) - 1) / 60, 0)

 

[JOGEC]

Thank you Shamima! This worked perfectly for what I was trying to do.

 

[shamimarita]

You are most welcome. I'm glad to hear that it worked perfectly. Keep helping each other in the forum to make the community stronger.