Forum Discussion
call time conversion
call duration was from a call and while collecting the data there different formats. I am trying to convert all the different formats and create a common format represent them.
| Call duration | Call duration in Sec |
| 0:16:56 | 1016 |
| 08:57 sec | 08:57 sec |
| Raw data | Expected output |
I am trying to get 537 for 08:57 sec but the formatting is not same
4 Replies
- There seems to be a string " sec" trailing the time. Assuming the meaning is 8 minutes and 57 seconds you could get the five leftmost characters
=LEFT(A1,5)
to exclude the string but you have to append the hours to have it an Excel time stamp;
=("00:" & LEFT(A1,5))
Now that it looks like a time stamp you can multiply with hours, minutes and seconds a day to have it result in the number of seconds;
=("00:" & LEFT(A2,5))*60*60*24bosinander As soon as I remove the sting it 5:57 becomes 5 hours and 57 minute. but it should be 8 minutes and 57 seconds
Arif_Awasaf_Aquib Yes, it is because time format expects hh:mm:ss and the format you receive is without hours.
Thus, instead of multiplying with 24*60*60 (86400 as Jihad Al-Jarady mentioned) you can multiply with 24*60 as attached.
- Hi,
you would use this formula to convert time to number as you mentioned 08:58 will be 537
=INT(A1*86400)
