Time and Duration

[Joe976]

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.

 

[Fluff]

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]

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]

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]

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]

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]

If you need to have the time in decimal format, why not just a helper column with
=A2*24

 

[Joe976]

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]

You're welcome & thanks for the feedback

 

[Joe976]

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.