Need help converting a total elapsed time string into hours

Faaez Kamaal

I am incredibly stumped by this problem I am needing to solve.

I have a string of elapsed time, "1mos 2w 1d 1h 43m 14s", and I need to convert it into HH:MM. And depending on the duration, we do not go beyond the "m" (minute) mark. So, "mos", "w", "d", and even "h" would not be as part of the output on my data source (outside of Smartsheet). Also, leading zeroes are not in the output either, which in my honest opinion would make things a little easier.

EG: "43m 14s" or "2w 0d 1h 43m 14s" are variations of the output.

So far, I have columns for my input values as [Total Ticket Hours], then I broke it down by each designator, Month, Week, Day, Hour, Minute, and Second.

Those formulas look like this:

=IFERROR(LEFT([Total Ticket Hours]@row, FIND("mos", [Total Ticket Hours]@row) - 1), "")

=IFERROR(MID([Total Ticket Hours]@row, FIND("w", [Total Ticket Hours]@row) - 1, 1), "")

=IFERROR(MID([Total Ticket Hours]@row, FIND("d", [Total Ticket Hours]@row) - 1, 1), "")

=IFERROR(MID([Total Ticket Hours]@row, FIND("h", [Total Ticket Hours]@row) - 2, 2), "")

=IFERROR(MID([Total Ticket Hours]@row, FIND("m", [Total Ticket Hours]@row) - 2, 2), "")

=IFERROR(MID([Total Ticket Hours]@row, FIND("s", [Total Ticket Hours]@row) - 2, 2), "")

However, I'm running into an issue where FIND is looking at the "m" for "mos" and is throwing some of my breakdowns off. Too bad I can't using literal strings (single quotes) vs regular strings (double quotes) to help with that.

Any suggestions? Also, any optimizations for the above formulas would be greatly appreciated.

ker9

Hi @Faaez Kamaal

Try these:

Months: =LEFT([Total Ticket Hour]@row, FIND(" ", [Total Ticket Hour]@row) - 4)

Weeks: =MID([Total Ticket Hour]@row, FIND(" ", [Total Ticket Hour]@row) + 1, FIND("w", [Total Ticket Hour]@row) - FIND(" ", [Total Ticket Hour]@row) - 1)

Days: =MID([Total Ticket Hour]@row, FIND("w", [Total Ticket Hour]@row) + 1, FIND("d", [Total Ticket Hour]@row) - FIND("w", [Total Ticket Hour]@row) - 1)

Hours: =MID([Total Ticket Hour]@row, FIND("d", [Total Ticket Hour]@row) + 1, FIND("h", [Total Ticket Hour]@row) - FIND("d", [Total Ticket Hour]@row) - 1)

Minutes: =MID([Total Ticket Hour]@row, FIND("h", [Total Ticket Hour]@row) + 1, FIND("m ", [Total Ticket Hour]@row) - FIND("h", [Total Ticket Hour]@row) - 1)

(Note that the "m " above in the Minutes formula includes a space)

Seconds: =MID([Total Ticket Hour]@row, FIND("m ", [Total Ticket Hour]@row) + 1, LEN([Total Ticket Hour]@row) - FIND("m ", [Total Ticket Hour]@row) - 1)

Once you see that these are working then wrap them in an IfError to return 0 if there are no months/weeks/days, etc. Example using Month: =IFERROR(LEFT([Total Ticket Hour]@row, FIND(" ", [Total Ticket Hour]@row) - 4), 0)

You will still have a problem calculating time for months since they have different number of days. It would help if you had a start and end date.

Hope this helps a little!

ETA: Sorry - If you only have minutes and nothing else, it will not work correctly. The minutes will appear in the seconds column.