ISO Duration format to google sheets

Question

please tell me how to convert the ISO 8601 duration (P2Y3M4DT1H2M1S) format into hours in google sheets, provided that some letters may not be (P1D, PT2H) and so on. Maybe there is a formula in google sheets? Thank you in advance

Tried via chatgpt but without success

Answer 1

I believe I have solution for you, although it is unclear what format you want it in exactly.

=LET(stamp,REGEXEXTRACT(A1,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?"),
IFNA(LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),
LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds,
LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds))))),))

Tested with P3Y6M4DT12H30M5S returns 108:30:5

Breaking this down into sections:

IFNA(..., )

Means if it doesn't match the regular expression it then returns a blank cell.

REGEXEXTRACT(A1,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?")

Extracts all the relevant numbers into an array. Regular Expression taken from here.

LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),

Extracts the numbers at those specific positions.
Note, the year and month are not later used as you cannot tell what month or year it is to be accurate.

LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds

This simply converts all the relevant values into seconds. If you wish to do some other format, starting with seconds allows you to change it easier.

LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds

This converts the seconds into the format of HH:MM:SS with hours exceeding 24 allowed, and decimal seconds also permitted.

---

A version that can work on an entire range at once is below:

=MAP(A1:A6,LAMBDA(full_stamp,LET(stamp,REGEXEXTRACT(full_stamp,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?"),
IFNA(LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),
LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds,
LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds
))))),))))

Just change A1:A6 to the relevant range.