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]

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

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

are you sure this doesn't work?

What is in the cell you are trying to convert--just a time value hh:mm:ss or is it a date/time value mm/dd/yyyy hh:mm etc.?

 

[CWatts]

You can use this formula:

=INT(A1)*24+HOUR(A1)+MINUTE(A1)/100

However, if you are doing any math, you're going to run into problems becuase 45 seconds is based on 60 seconds where decimals are based on 100.

If it were me, I'd reccommend changing the 45 minutes to show .75 which will represent three quarters of an hour.

Since this is typically a call center metric, and most call centers bill by talk time, you could be shorting yourself billable time depending on how you're using this. if it's just for display purposes, then no problem.

 

[paddydive]

It is in mm/dd/yyyy hh:mm format

 

[barry houdini]

This should also work

=INT(A1*24)+MINUTE(A1)/100

but if the seconds are 59 that will round down to the previous minute.....to show the nearest minute try

=INT(A1*24)+MOD(A1*24,1)/10*6

either way format as number with 2 decimal places

 

[paddydive]

Hey CWatts,

Thanks ....You hit the nail on the head

I need this for call centre metric only.. and your formula worked...

Can you explain me how it works...

 

[paddydive]

Hey barry houdini,


Thanks you for help, that worked, can you explain how the formula
MOD(A1*24,1)/10*6 works...

 

[CWatts]

When you put that large a number of hours, excel converts hhhh:mm:ss to mm/dd/yy hh:mm:ss so it shows up as:

6/26/1900 2:45:21 PM

1900 is the start date of excel serial dates.

The integer of a serial date is the number of days, so the INT() command takes the number of days and multiplies it by 24 hours in a day. We then add the remaining hours with HOUR() and then we use MINUTE() to get the minutes and divide them by 100 becuase you wanted them to display as a decimal.

Changing to this will round up the seconds, too if you need.

=INT(A7)*24+HOUR(A7)+MINUTE(A7)/100+IF(SECOND(A7)>=30,0.01,)

 

[paddydive]

Thanks,

You Guys are amazing and i know i will always get solution to all my excel problem in this forum....

Appriciate all your help and patiance.

God Bless you