Convert h:m:s to just minutes in Excel

MPWOOD
MPWOOD used Ask the Experts™
on
I have a list of time values in Excel which are formatted in hours, minutes and seconds as h:m:s, e.g. 1:01:21

I need to convert them all to minutes so that I can perform a calculation on the cells using a formula in a new column.

the Convert function doesn't seem to work as this just converts (say) hours to minutes, but my cells contain hours minutes and seconds.

Is there another way of doing this??

In the attached file, column B needs converting so that I can divide cost by minutes in column C
Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if your time is in a1 then you can do it with this
=A1*24*60

Author

Commented:
Hi HaiFai, that formula doesn't work for me, I think it's to do with the formatting of the cells - see in the attached sample.
How is column B being created?  Its not is proper DATE format...
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

This will give you the minutes

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"h",":"),"m",":"),"s",""))*24*60

Author

Commented:
Coumn B is derived from Column A, replacing the notation of H, M and S with colons... excel is seeing it as text I think instead of as a time field.  

the column isn't an actual time however e.g. 13:40:40 - being twenty to two in the afternoon but thirteen hours, forty minutes and forty seconds...hope that makes sense.  I'm trying to find out the cost per minute and to do that I need to convert the field to minutes - in this case it would be 820 minutes (and forty seconds)
=c2/(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"h",":"),"m",":"),"s",""))*24*60)

Open in new window


This will calculate qwhat you are looking for

Author

Commented:
Genius - I love this forum!  Thanks ssaqibh!

Author

Commented:
thanks also TazDevil1674 for your solution which is essentially the same as the one I just accepted before I saw your post, sorry!
Thanks...  All I did was clarify the last piece of your puzzle...

Commented:
yes you need to have cell format general where this is =A1*24*60

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial