Convert Xmos Xw Xd Xh Xm Xs format to calendar days

[Dentine]

Hello all,

I receive a report with data as listed below. I've found a number of formulas that will convert Xd Xh Xm Xs to an HH:MM format, however, I have not found anything that can account for the Xw (weeks) or Xmos (months). I can edit from HH:MM to calendar days myself, but have not been able to format any entry with Xw or Xmos included. With ~8,000 entries, doing this manually is not an option. If anyone can suggest an option, it would be greatly appreciated.

54m 22s

55m 19s

1h 2m 57s

1h 11m 18s

2w 3d 5h 22m 0s

2w 3d 4h 19m 56s

1mos 0w 0d 15h 26m 51s

1mos 0w 0d 15h 21m 51s

 

[Dave Patton]

What result do you require?
Do you want the result in equivalent days and if so is a month = 30 days?

 

[Dave Patton]

No additional information was received.
The following may help.

Time.xlsm
	A	B
1
2
3	54m 22s	00:54:22
4	55m 19s	00:55:19
5	1h 2m 57s	01:02:57
6	1h 11m 18s	01:11:18
7	2w 3d 5h 22m 0s	17:5:22:0
8	2w 3d 4h 19m 56s	17:4:19:56
9	1mos 0w 0d 15h 26m 51s	30:15:26:51
10	1mos 0w 0d 15h 21m 51s	30:15:21:51
11		95.85733796
12
4a

Cell Formulas
Range	Formula
B3:B6	B3	=--IF(ISERROR(FIND("h",A3)),TEXTJOIN(":",,TEXTSPLIT("0h"&A3,{"h","m","s"})),TEXTJOIN(":",,TEXTSPLIT(A3,{"h","m","s"})))
B7:B8	B7	=TEXTBEFORE(A7,"w")*7+TEXTAFTER(TEXTBEFORE(A7,"d")," ")+LET(t,TEXTAFTER(A7,"d "),--IF(ISERROR(FIND("h",t)),TEXTJOIN(":",,TEXTSPLIT("0h"&t,{"h","m","s"})),TEXTJOIN(":",,TEXTSPLIT(t,{"h","m","s"}))))
B9:B10	B9	=TEXTBEFORE(A9,"mos")*30+TEXTBEFORE(TEXTAFTER(A9,"mos "),"w")*7+TEXTAFTER(TEXTBEFORE(TEXTAFTER(A9,"mos "),"d")," ")+LET(t,TEXTAFTER(TEXTAFTER(A9,"mos "),"d "),--IF(ISERROR(FIND("h",t)),TEXTJOIN(":",,TEXTSPLIT("0h"&t,{"h","m","s"})),TEXTJOIN(":",,TEXTSPLIT(t,{"h","m","s"}))))
B11	B11	=SUM(B3:B10)

 

[Dentine]

Apologies for the delayed response. The formulas provided are perfect! Thank you!

 

[Dave Patton]

Thanks for the feedback.
You may be able to assemble the formulas into one so that you can fill or copy down for the relevant range of data.