We help IT Professionals succeed at work.

Excel 201  Convert minutes & seconds to decimals

Jeremy-M
Jeremy-M asked
on
382 Views
Last Modified: 2013-12-10
How can I convert minutes and seconds to decimals, for example, 21:42 (21 minutes:4 seconds is .3671 of an hour.  What is the formula and what is the cell formatting to convert minutes:seconds to decimals?

Thanks,

Ed
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Divide the time by 2.5    and format the cell as general or fixed,

2.5=60/24

Author

Commented:
What is the cell format for 21:42?

What is the cell format for the formula?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
21:42 can be either in time format or in text format
Steven CarnahanAssistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
If you use the time format then you should put it in 00:21:42 format or it will read as hours/minutes instead of minutes/seconds.
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Thank you pony10us for correcting me. my formula was converting 21hrs... to decimals.

Actually you should be dividing the time by 24.

Author

Commented:
I formatted the cell as "hh:mm:ss" and the Error Message displayed below appeared when I depress the tab key and attempted to exit the cell.

Error Message for "hh:mm:ss" Cell Format
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Can you include the formula bar and the cell in the snapshot?
Steven CarnahanAssistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
np - however I think it would be to multiply by 24

00:24:15 * 24 = 0.404167
24:15:00 * 24 = 24.25   (24 and 1/4 hours)
23:15:00 * 24 = 23.25   (23 and 1/4 hours)
23:45:00 * 24 = 23.75   (23 and 3/4 hours)

Actual formula would be:

=a1*24  (a1 being the cell that contains the time)

Author

Commented:
I'm still getting the error message

I can't upload an image large enough to be readable

Cell Format:          Custom "hh:mm:ss"

Entering "00:21:42: in the cell results in the standard Excel Error Message

"The formula you type contains an error:
blah, blah, blah"


What is wrong?

 
Cell Format - Cell Contents - Error Message
Steven CarnahanAssistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
Column A is format time hh:mm:ss
Column D is format general
Formula is in column D as shown at top of screen cap

time decimal conversion

Author

Commented:
The format I am using in Column A is "hh:mm:ss" and when I enter "00:21:42" or "0:21:42" I receive the error message when I depress the tan key in an attempt to exit the cell the Excel error message is displayed.

This should not be this difficult!
Assistant Vice President\Network Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.