Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Excel 201 Convert minutes & seconds to decimals

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
0
Jeremy-M
Asked:
Jeremy-M
  • 4
  • 4
  • 4
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Divide the time by 2.5    and format the cell as general or fixed,

2.5=60/24
0
 
Jeremy-MAuthor Commented:
What is the cell format for 21:42?

What is the cell format for the formula?
0
 
Saqib Husain, SyedEngineerCommented:
21:42 can be either in time format or in text format
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
pony10usCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
Thank you pony10us for correcting me. my formula was converting 21hrs... to decimals.

Actually you should be dividing the time by 24.
0
 
Jeremy-MAuthor 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
0
 
Saqib Husain, SyedEngineerCommented:
Can you include the formula bar and the cell in the snapshot?
0
 
pony10usCommented:
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)
0
 
Jeremy-MAuthor 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
0
 
pony10usCommented:
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
0
 
Jeremy-MAuthor 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!
0
 
pony10usCommented:
Here is the full spreadsheet
EEtd.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now