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
Jeremy-MAsked:
Who is Participating?
 
Steven CarnahanNetwork ManagerCommented:
Here is the full spreadsheet
EEtd.xlsx
0
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Saqib Husain, SyedEngineerCommented:
21:42 can be either in time format or in text format
0
 
Steven CarnahanNetwork ManagerCommented:
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
 
Steven CarnahanNetwork ManagerCommented:
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
 
Steven CarnahanNetwork ManagerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.