Converting Time in Excel 2007

[ladyck3]

I know how to convert 0:09:23 (H:MM:SS) into 9.x Minutes via *86400/60 but for the life of me I'm having very much of a problem comprehending how to, once it is converted, revert back to the H:MM:SS format.

So, if I have 9.3 minutes, how many seconds is the .3?

HELP

Laurie

ladyck3
aka: Laurie

 

[SkipVought]

if your TIME data is in A1...
[tt]
=A1*24*60
[/tt]
will return 9.383333

That is DAYS * 24 hrs per day * 60 min per hrs

What do you need?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

60 x 0.3 = 18

 

[SkipVought]

to CONVERT DAYS > HRS > MINUTES

=x*24*60

and to REVERT MINUTES > HRS > DAYS

=x/24/60

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

This is trying to figure Average Handle Time (AHT)
If there are 5216 calls that took 491,692 minutes,
Column B
Line1 Calls 2617
Line2 Total Minutes 24965
Line3 AHT b2/b1 = 9.5 minutes but what is the TRUE TIME? This is too easy .5 minutes is 00:09:30 seconds

If the AHT was 9.3 how via the calculation in B3 could I convert the results to come out in true minutes?

Thanks in advance...


ladyck3
aka: Laurie

 

[SkipVought]

Excel Date/Time is stored in units of DAYS.

You can find the DIFFERENCE between two Date/Time values to calculate a DURATION.

That DURATION is stored in units of DAYS as well.

You can convert DAYS to whatever units you so desire. Its not rocket science. You just have to keep track of exactly what you're working with: Days? Hours? Minutes?

So what's the REAL question?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Excel's unit of time is the DAY. So work with days.

Convert your minutes to days.

Minutes / (24 * 60) = days.

So make your B3 formula =B2/(B1*24*60) and format B3 as time.

 

[Lilliabeth]

I think
=B2/B1/24/60
then format as [h]:mm:ss ???

--Lilliabeth