Search code examples
exceltexttimenumeric

How to convert "dd:hh:mm:ss" format to numeric type On Excel?

With the columns 'start at" , "end at" , I know the 'ride length' which I get from D-C in hh:min:sec format.I also found in day:hour:min:sec format by =TEXT(D12-C12,"d:h:mm:ss") formula. This is in a text format, I want it to be a numeric format so that I can find min,max,avg ride length.

I tried the several solutions like converting text to numeric.This is not working.

C D E F
start_at end_at ride_length(hour:min:sec) ride_length(day:hour:min:sec)
31-10-2021 11:38:30 PM 03-11-2021 3:53:21 PM 64:14:51 2:16:14:51
31-10-2021 11:47:46 PM 02-11-2021 12:47:42 AM 24:59:56 1:00:59:56
Solution

  • if you are using excel it is not necessary to use the TEXT formula to express the number of days. Instead place the result of the D-C subtraction and change the cell format to d "d" h "h" m "m" s "s" (see Figure 1)

    Figure 1