Change exported dates to values and calculate average

[NewOrderFac33]

Good afternoon,

I have exported data which contains a column showing durations in the following (text) format:

1d 3h 50m 32s

0d 0h 0m 47s

0d 1h 10m 46s

0d 1h 39m 50s

0d 0h 0m 43s

0d 0h 9m 29s

0d 0h 41m 27s

I would like to be able convert these labels into time values, in order to calculate the average for the column - can anyone suggest how I might be ale to accomplish that, please?

Thanks - Pete

 

[Cubist]

Try:

Book1
	A	B	C
1	String	Days	[h]:mm:ss
2	1d 3h 50m 32s	1.160	27:50:32
3	0d 13h 0m 47s	0.542	13:00:47
4	0d 1h 10m 46s	0.049	1:10:46
5	0d 1h 39m 50s	0.069	1:39:50
6	0d 0h 0m 43s	0.000	0:00:43
7	0d 0h 9m 29s	0.007	0:09:29
8	0d 0h 41m 27s	0.029	0:41:27
9	Average	0.265	6:21:56
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=--LEFT(A2,FIND("d",A2)-1)+MID(A2,FIND("h",A2)-2,2)/24+MID(A2,FIND("m",A2)-2,2)/1440+MID(A2,FIND("s",A2)-2,2)/86400
C2:C8	C2	=--LEFT(A2,FIND("d",A2)-1)+MID(A2,FIND("h",A2)-2,2)/24+MID(A2,FIND("m",A2)-2,2)/1440+MID(A2,FIND("s",A2)-2,2)/86400
B9:C9	B9	=AVERAGE(B2:B8)

 

[NewOrderFac33]

=--LEFT(A2,FIND("d",A2)-1)+MID(A2,FIND("h",A2)-2,2)/24+MID(A2,FIND("m",A2)-2,2)/1440+MID(A2,FIND("s",A2)-2,2)/86400

Absolutely spot on - thank you VERY much!

 

[NewOrderFac33]

Absolutely spot on - thank you VERY much!

Mostly working, but I seem to be getting errors for anything that goes over a month thus:

45d 1h 27m 44s	14 days 01 hrs 27 m 44 s
42d 7h 26m 33s	11 days 07 hrs 26 m 33 s
45d 7h 23m 30s	14 days 07 hrs 23 m 30 s
20d 7h 22m 14s	20 days 07 hrs 22 m 14 s

The top three have 31 days subtracted from them, whereas the bottom one is correct (all use your formula) - any thoughts? Thanks

 

[Cubist]

I don't understand what you're showing me. Your left column works fine as it matches the same structure as the sample provided in the OP. Since it's a number, I can subtract it from 31. Where did the right column come from?

Book1
	A	B	C	D
1	Time String	Days	Days -31
2	45d 1h 27m 44s	45.06093	14.06093
3	42d 7h 26m 33s	42.31010	11.31010
4	45d 7h 23m 30s	45.30799	14.30799
5	20d 7h 22m 14s	20.30711	-10.69289	<- don't have enough 31 days, what do you expect in this case?
6
7	14 days 01 hrs 27 m 44 s	#VALUE!	<- not in the same format #d #h #m #s
8	11 days 07 hrs 26 m 33 s	#VALUE!	<- not in the same format #d #h #m #s
9	14 days 07 hrs 23 m 30 s	#VALUE!	<- not in the same format #d #h #m #s
10	20 days 07 hrs 22 m 14 s	#VALUE!	<- not in the same format #d #h #m #s
Sheet2

Cell Formulas
Range	Formula
B2:B5,B7:B10	B2	=--LEFT(A2,FIND("d",A2)-1)+MID(A2,FIND("h",A2)-2,2)/24+MID(A2,FIND("m",A2)-2,2)/1440+MID(A2,FIND("s",A2)-2,2)/86400
C2:C5	C2	=B2-31

 

[NewOrderFac33]

The left column contains strings, the right column shows the values that your formula returns. So, it works OK day-wise as long as the number of days in the string is less than 31 (the fourth value in the list), but if the number of days in the string exceeds 31, 31 days are subtracted from the "Day" part of the result e.g. 45 returns 14, 42 returns 11 and so on

45d 1h 27m 44s	14 days 01 hrs 27 m 44 s
42d 7h 26m 33s	11 days 07 hrs 26 m 33 s
45d 7h 23m 30s	14 days 07 hrs 23 m 30 s
20d 7h 22m 14s	20 days 07 hrs 22 m 14 s

 

[Cubist]

EDIT:
I believe this because the dd formatting is limited to days of the month for 31 days even though the underlying number is correct. You might have to split it up into 2 cells to show the day and time separately.

 

[Cubist]

Book1
	A	B	C	D
1	Time String	Total	Day	Time
2	45d 1h 27m 44s	14:01:27:44	45	01:27:44
3	42d 7h 26m 33s	11:07:26:33	42	07:26:33
4	45d 7h 23m 30s	14:07:23:30	45	07:23:30
5	20d 7h 22m 14s	20:07:22:14	20	07:22:14
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=--LEFT(A2,FIND("d",A2)-1)+MID(A2,FIND("h",A2)-2,2)/24+MID(A2,FIND("m",A2)-2,2)/1440+MID(A2,FIND("s",A2)-2,2)/86400
C2:C5	C2	=INT(B2)
D2:D5	D2	=MOD(B2,1)

 

[NewOrderFac33]

Legend - we'll give it a try, but looks good Have a great weekend!

 

[Dave Patton]

N.B. My system has Regional Settings of dd-mmm-yy; but I do not see why this makes a difference.

The results show the correct number of days; the "d" part just yields a number.
I do not see why the formula is showing 14 instead of 45.

Dates and Time 2024.xlsm
	A	B	C	D
1			Excel 365
2	Time String	Total
3	45d 1h 27m 44s	45.060926	45.0609259	45.0609259
4	42d 7h 26m 33s	42.310104	42.3101042	42.3101042
5	45d 7h 23m 30s	45.307986	45.3079861	45.3079861
6	20d 7h 22m 14s	20.307106	20.3071065	20.3071065
7
8	50d 1h	50.0416667	50.0416667
4g

Cell Formulas
Range	Formula
B3:B6	B3	=LEFT(A3,FIND("d",A3)-1)+MID(A3,FIND("h",A3)-2,2)/24+MID(A3,FIND("m",A3)-2,2)/1440+MID(A3,FIND("s",A3)-2,2)/86400
C3:C6	C3	=TEXTBEFORE(A3,"d")+RIGHT(TEXTBEFORE(A3,"h"),2)/24+RIGHT(TEXTBEFORE(A3,"m"),2)/(24*60)+RIGHT(TEXTBEFORE(A3,"s"),2)/(24*60*60)
D3:D6	D3	=TEXTBEFORE(A3,"d")+TEXTJOIN(":",,TEXTSPLIT(TEXTAFTER(A3,"d "),{"h ","m ","s"}))
B8	B8	=LEFT(A8,FIND("d",A8)-1)+MID(A8,FIND("h",A8)-2,2)/24
C8	C8	=TEXTBEFORE(A8,"d")+RIGHT(TEXTBEFORE(A8,"h"),2)/24