Avatar of MPWOOD
MPWOOD
 asked on

Convert h:m:s to just minutes in Excel

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
Microsoft Excel

Avatar of undefined
Last Comment
HaiFai

8/22/2022 - Mon
HaiFai

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

ASKER
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.
TazDevil1674

How is column B being created?  Its not is proper DATE format...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MPWOOD

ASKER
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)
TazDevil1674

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

Open in new window


This will calculate qwhat you are looking for
MPWOOD

ASKER
Genius - I love this forum!  Thanks ssaqibh!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MPWOOD

ASKER
thanks also TazDevil1674 for your solution which is essentially the same as the one I just accepted before I saw your post, sorry!
TazDevil1674

Thanks...  All I did was clarify the last piece of your puzzle...
HaiFai

yes you need to have cell format general where this is =A1*24*60
Your help has saved me hundreds of hours of internet surfing.
fblack61