Time and Duration

Joe976

New Member
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.

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
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
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
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
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
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
If you need to have the time in decimal format, why not just a helper column with
=A2*24

Joe976

New Member
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
You're welcome & thanks for the feedback

Joe976

New Member
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.