I need to convert a time format to decimal format. The time format is a duration of time and not a time of day. It's set up as HHHH:MM:SS and I would like to be able to convert it to the decimal format. For example 0002:30:30 would be 2.5 or if you extended it 2.508. The time format is set by a program that exports the info into an excel file so no changing the number of characters in the time.

Hi, Time in Excel, whether time of day or duration is held as the decimal part of the value in the cell.

12 hours or 12noon is 0.5 which is half way through the day.

Extract the decimal part of the value. If you are calculating duration, and your duration goes beyond 24 hours then there will be whole numbers as well with 1 equivalent to 24 hours.

Just staying with values less than 24 hours:

If the time is in cell A1 use =MOD(A1,1) to get only the decimal part of the contents of A1

To get your decimal part of the hour use

=24*MOD(A1,1)Your time/duration value of 02:30:30 returns 2.508333 using the above formula.

24*A1 will work if you have durations longer than 24 hours. If cells are formatted to only show hours / minutes /seconds, there may be date information in the cell, so the Modulus approach which uses only the decimal part of the cell contents may be more robust.

All result cells need to be formatted as a number with the required number of decimal places - Excel tends to try and carry over the time format.

Regards

or, just for fun... =TEXT(LEFT(A1,4),"####")&TEXT(MID(A1,FIND(":",A1)+1,2)/60,".##")&RIGHT(TEXT(RIGHT(A1,2)/60/60*10,".##"),2)

Humar-

Thanks for the reply but it doesn't work becasue the time format has space for four number in the hour portion of the time format.DerbyDad03-

Thank you also for the reply and yours works to an extent. I'm guessing that what it does is change the formating of the cell to text rather than numerical. But whatever the case it creates another problem. It will not allow me to SUM the the answer for a total. Any ideas?

OK, just figured it out. I added parentheses around the formula and +0 at the and. It works. =(TEXT(LEFT(A1,4),"####")&TEXT(MID(A1,FIND(":",A1)+1,2)/60,".##")&RIGHT(TEXT(RIGHT(A1,2)/60/60*10,".##"),2))+0

re: " It works"Yes, but do you know

whyit works? ;-)

I believe I do. Your formula returned the answer as text. My addition to the formula changed it back to a numerical value. Correct?

Hi, I see why my solution did not work - it is because your data is not a real Excel time, it is just a text string in the format HHHH:MM:SS

If you go to cell formatting is it in General format or Text format rather than one of Excel's time formats such as hh:mm:ss

If you used Excel's time format you could easily convert the values and you could add/subtract times.

You can format Excel to show hours greater than 23 by using the [h] formatting, so 1 day 2 hours shows as 26 hours.

Regards

re: My addition to the formula changed it back to a numerical valueYes. Since Excel is a "numbers program" at heart, it will do its best to covert anything that could be a number into a number when you try to perform a mathematical operation on it.

However, you need to test my formula with some more of your values. It appears that it doesn't always work.

I dragged 0002:30:30 down a few cells and when it got to 0002:30:35 it returned 2.58.1 which I doubt is what you want.

I'll need to see if there is anything that can be done about that.

Not completely sure what you want, but look at the =TIMEVALUE() function. This function will show an actual time based on a piece of text which

lookslike a time.If A1 = 0002:30:30 then you first need to make the TEXT string look like a time, to do that delete the leading zeros so you get the text string: A1 = 2:30:30

In B1 enter =TIMEVALUE(A1) and you will get 0.104513889

In C1 enter B1* 24, as Humar pointed out, and you get 2.508333333

NOW, the interesting thing is that as long as the leading number of your text string is

NOTa zero you can push this out at least three digits.BUT as best as I can figure at the moment, you can only go to 119:59:59, which will give you 23.99972222

Hopefully, some else can take it from here.....

MIKE

Ask Your Question

Weekly Poll

What do you think of Volkswagen's diesel settlement?

Discuss in The Lounge

Poll History