convert hh:mm:ss to mm

[24jedi]

Hello,

I am trying to convert the total duration of time (hh:mm:ss) into jus
minutes. As an example, a phone bill.

The duration of a phone call is 2:12:33, 2 hours, 12 minutes, 3
seconds. The charge for the call was $ 4.05. I would like to conver
2:12:33 into minutes so that I can calculate the cost of the call pe
minute.

How I think it would look in the spreadsheet.

(a1) 2:12:33
(a2) 4.05
(a3) {some formula}

Thank

 

[Jason Morin]

Use:

=(A1*1440)*A2

where 1440 converts to the time to minutes. Because time
values are actually fractional days (e.g., .5 = 1/2 day),
multiplying by 1440 converts it to minutes:

..09204861 days (2:12:33) x 24 hr/day x 60 min/hr =

132.55 minutes.

HTH
Jason
Atlanta, GA

 

[JE McGimpsey]

XL stores times as fractional days (e.g., 3:00:00 = 0.125), so to get
minutes, multiply by 24*60, or 1440. So for cost per minute, one way:

A3: =A2/(A1*1440) ===> $0.031

 

[Jason Morin]

Oops, misread your post. Use J.E.'s formula. I was
thinking you were looking for a total cost at $4.05 per
min.

Jason

 

[24jedi]

The formula is working...Thanks

But, I am having a minor appearance problem.

With the cell formated as number, when I type in 9:23:42 into a cell
EXCEL automatically changes the value to the the serial value 0.39.

This is going to confuse the person actually reviewing the worksheet.

Got any ideas ??

Thanks again for the help so far

 

[24jedi]

::bump:

 

[JE McGimpsey]

::bump::

:lonk::

 

[24jedi]

JE, (or anyone else)

my bump was to hopefully reposition my post so it could be seen again
Several new posts came in after mine.

Since you initially posted a solution for me (thanks), I was hopin
someone could help me with the second half.

Is there a workaround to prevent EXCEL from automatically convertin
hh:mm:ss to the serial value ?

Thank