Date and Time - Just need time please

[chrisbn7]

Hi all,

I have cell A1 displaying 1450:34:00 formatted with the custom format [h]:mm:ss

When I click on A1, the formula bar tells me that the cell entry is

29/02/1900 10:34:00

The value of 1450:34:00 is actually the total number of minutes:seconds:and presumably milliseconds people are logged into a telephone system. I don't need the 00 after the second : and just need the 1450:33 bit i.e. 1450mins 33secs. I then wanted to convert this to hh:mm:ss format, so in this instance it'd be 24h10m34s

In cell A2 I have the total number of calls taken during that time period, 122

I want to be able to do the calculation in A3 that will give me the result of 122/24:10:34 and find out what the average number of calls is per minute or per hour. The answer I can work out on a calculator as 5 and a bit.

However my sheet has about 500 rows per day in it, and I need to do this in calculation for each row.

Can anyone help at all please?

Thanks,
Chris

 

[Jonmo1]

Welcome to the board...

To extract only the time from a cell containing date and time...

Try
=MOD(A1,1)

Hope that helps.

 

[barry houdini]

Hello Chris,

Excel is actually seeing 1450:34:00 as 1450 hours and 34 minutes. To get that to minutes you'd divide by 60.

In A3 you can use this formula to get calls per hour (the formula includes the conversion of A1 to minutes rather than hours)

=A2/(A1/60*24)

format A3 as general