How to convert HH:MM:SS to integer

[paddydive]

Hi,

I am working in a data where i have to convert the talk time consumed by a agent to a number.

The talk time is in HH:MM:SS format which i want to convert in number with 2 decimal

For example if the Talk time is 4286:45:21 i should get it in number format which will display like 4286.45 where 4286 will be hours and .45 will be minutes.

I know this is a very stupid question but i am not able to find a simple formula for it.

Thanks in advance.

 

[Desu Nota from Columbus]

Create a custom format: Number Format>Custom>Type in:

[h].mm

 

[paddydive]

Hi Desu Nota,

Thank you very much for such a quick reply.

I had tried this before, but changing just the format will keep the cell again in Time format only.

I want to convert it completly to a number, so that even if i change the number format to general it should not change.

Can u plz help me in this pls

 

[mikerickson]

Try
=TEXT(A1, "H.MM")+0

 

[Desu Nota from Columbus]

=TEXT(A1,"[H].MM")+0


make sure the column where you put this is formatted as general

didn't refresh the page to see the first post...sniped!

 

[paddydive]

Hi mikerickson & Desu Nota,

Sorry but this formula is changing the value now, after using the formula the value is changing to 346:48:00

And even if i use this formula in a cell with General number format, after the formula it is automatically changing to time format again.

 

[mikerickson]

Change the format of the cell back to general.

 

[paddydive]

mikerickson

Done...But now it is showing it as 14.45. Am i missing somthing to explain.
Yes.. the cell which we are converting to number is a sum of 10 records which all has the same hh:mm:ss format. But when we sum this 10 records and convert the sum formula to value, it is showing it as
"6/26/1900 02:45:21 PM"

Is this something to do with it.

 

[mikerickson]

my mistake, the formula should be =TEXT(A1, "[H].MM")+0

 

[paddydive]

Opps...

Sorry for late replying was stuck with some worki but it still not working... Mikerickson.

can we use =Int formula for that, I have tried it but i think it is calculating on Hours only, what is the formula to Sum only decimals.