Changing from day/hour/minute to a number

Question

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.
&nbsp;
How can I transform that time entry to the number, can anyone help here?
&nbsp;
Thanks so much!

sergeibaklan · Answer

Hi,
&nbsp;
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

haytham amairah · Answer

Hi,
&nbsp;
Please try this formula and find it in the attached file:
=LEFT(A1,2)+ROUND((RIGHT(A1,8)),1)&nbsp;

Forum Discussion

Changing from day/hour/minute to a number

Share

Resources