Time and Duration

Joe976

New Member
Joined
Sep 16, 2019
Messages
7
First, a Disclaimer. I am absolutely 100% new to working with Excel. This exercise I am having an issue with is my first time playing with cell formatting and formulas.

What I want to do, which sounds simple in my head, is input an amount of time into a cell, but have it display as its decimal equivalent. So, if I put 2 hours 30 minutes into cell A2 in the format of '2:30', I would like it to convert that to '2.50' and display it in cell A2. The reasons I am looking for this to happen, is so the displayed 2.50 can be used in other equations throughout the sheet without this funky time thing Excel seems to have.

I am sorry if this question has been answered already. I did try searching, but I must be using wrong keywords. Any advice is greatly appreciated.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,853
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Changing the format of the cell, does not change the underlying value. Therefore any formulae looking at A2 will see 2:30 not 2.5
 

Joe976

New Member
Joined
Sep 16, 2019
Messages
7
Hmmm. That's a stinker. Is there some way I can have a formula that converts the time to a decimal and outputs to the same cell? I think I tried to multiply the time by 24, but it would not work for the same cell. Is there a different work around that I can try?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,853
Office Version
365
Platform
Windows
A cell can have a formula or a value, but not both. You would need to use VBA to do that, although sorting out the correct formulae would probably be a better option.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
In Excel, the TIME portion of a date/time value is the decimal part of a number representing the number of days since "Jan 0, 1900"
So any conversions must consider that in the calculation/conversion.
So yes, multiply by 24 (hours). Remember that you may need to convert back down for certain output too.
 

Joe976

New Member
Joined
Sep 16, 2019
Messages
7
I guess the easiest way is to just use a calculator to figure the decimal equivalent of the minutes and enter the values as numbers. The easy ones like 15 minutes or 30 minutes would be great if its all I had. Sometimes however, I get oddball stuff like 22 minutes, or 39 minutes. The hours would stay the same, regardless. I did try the special paste option where I entered 24 into a blank cell, copied the cell to the clipboard and special pasted it with value and multiply checked into the cell I needed it in. It worked perfectly. The entire row populated with the correct data. But it only worked once. Perhaps I missed something there? I didn't confirm or save the special paste in the right manner? Thanks for the advice. If nothing else, it is steering me in another direction.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,853
Office Version
365
Platform
Windows
If you need to have the time in decimal format, why not just a helper column with
=A2*24
 

Joe976

New Member
Joined
Sep 16, 2019
Messages
7
Helper column? I will have to look into that. Not sure how that would work... Hmm, got my brain ticking though. I may be able to use that and still keep what I was looking for aesthetically. Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,853
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Joe976

New Member
Joined
Sep 16, 2019
Messages
7
Adding a helper column at somewhere at after the last column with that formula, then hiding the column worked perfectly. Did exactly what I was looking for. Thank you folks your help and advice.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,933
Messages
5,411,332
Members
403,362
Latest member
DoubleJay

This Week's Hot Topics

Top