Changing from day/hour/minute to a number

[null null]

Hi,

I have a large column of data from a databank giving the time needed to complete a report, given as "1 D 09:43:36", and I need to transform that to a number, i.e. 1.4 (days) and to calculate the mean.

How can I transform that time entry to the number, can anyone help here?

Thanks so much!

[#2]

Hi,

Better with Power Query but if with formulas could be like

=VALUE(LEFT(A1,SEARCH("D",A1)-1))+VALUE(RIGHT(A1,LEN(A1)-SEARCH("D",A1)))

to convert

[#3]

Hi,

Please try this formula and find it in the attached file:

=LEFT(A1,2)+ROUND((RIGHT(A1,8)),1)