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

 

[NewOrderFac33]

Good morning - I'm still getting this, using your formulae as above(we're on Office 2016, if that helps?)

Time String	Total	Day	Time
45d 1h 27m 44s	14:01:27:44​	14 days​	01 hrs 27 mins​
42d 7h 26m 33s	11:07:26:33​	11 days​	07 hrs 26 mins​
45d 7h 23m 30s	14:07:23:30​	14 days​	07 hrs 23 mins​
20d 7h 22m 14s	20:07:22:14​	20 days​	07 hrs 22 mins​

 

[NewOrderFac33]

Good morning - thanks for your suggestion, but we're using Office 2016, so the TEXTBEFORE function isn't available

 

[Cubist]

Change the formatting on the Day to General.

 

[NewOrderFac33]

Change the formatting on the Day to General.

That did it - thank you SO much!

 

[Cubist]

You're welcome.