Excel Time Formula

This is about the best I can come up with, it gives you the correct number of minutes, but separated by a dot, not a colon (if I find a solution to that part of it, I'll get back here with it):

=TRUNC(A4/100)+((A4/100-TRUNC(A4/100))*100/60)/100*60

I added to gtgloner's code. This should do it:
=SUBSTITUTE(TEXT(TRUNC(A4/100)+((A4/100-TRUNC(A4/100))*100/60)/100*60,"#######0.00"),".",":",1)

Make sure there's enough positions to the left of the decimal in the mask for your purposes.  "#######0.00" would fit any number up to 99999999.99.

Try =(LEFT(A1,3)*60+RIGHT(A1,2))/(1440)

See attached spreadsheet for formula.

Sorry, I didn't see your question modification.  My above post will convert your number to days - not hours and minutes.  However, if you only want to display 20915 asn 209:15, you can use the following formula.

=CONCATENATE(LEFT(A1,3),":",RIGHT(A1,2))

mhunts,
You can convert to days using a formula like:
=INT(A1/100)/24+MOD(A1,100)/1440              display as days using a Number format

This value may be displayed like 209:15 using Custom format like
[h]:mm

You may alternatively convert to hours:minutes displayed as text using:
=TEXT(INT(A1/100)/24+MOD(A1,100)/1440,"[h]:mm")

Brad

I tried all of your suggestions, but none of them yield the correct result.

Column A: 20915
Column B: 209:15 displayed, but stored as 209.25

I need the number to be displayed as hours:minutes, but stored in decimal minutes. The reason for this is that I need to do mathematical operations on the result in column B. Such as averages.

You can test your solution before postiing by adding a column C, which is column B + 1. The display result should be 210:15 (with a stored result of 210.25).

Thanks for all your suggestions. I think I wasn't clear enough in the question. The result has to be numeric.

Hi - this will do it...

It assumes that there's always two numbers in the minutes counter but it accounts for 0 minutes.

=IF(RIGHT(A1,2)="00",(LEFT(A1,(LEN(A1)-2))/24),(LEFT(A1,(LEN(A1)-2))/24)+(1/(60/(RIGHT(A1,2))*24)))

Cheers.

Sorry - forgot to add - you can custom format the above as dd:hh

Try this...

=text(LEFT(A1,3)&":"&RIGHT(A1,2),"[h]:mm")

This will do the trick for you...

You don't need the TEXT function if you do that:
=LEFT(A1,3)&":"&RIGHT(A1,2)
does the same thing, but neither solves the problem of displaying 209:15 while putting 209.25 in the cell (since it's impossible!)

If you go back to the original question...

Turn 21330 into 8.9 days displayed as 08:21

my formula with a custom format of dd:hh does exactly that:
=IF(RIGHT(A1,2)="00",(LEFT(A1,(LEN(A1)-2))/24),(LEFT(A1,(LEN(A1)-2))/24)+(1/(60/(RIGHT(A1,2))*24)))

Rory on second thoughts it can be done..even if he enters 209.25...it can be changed to 209:15....use this formula....

=text(LEFT(A1,3)&":"&RIGHT(A1,2)/100*60,"[h]:mm")

Out here since you enter in decimals which is at 100 scale..all you need to do is convert that to 60 scale..and i did the text formating to ensure that it works...

Saurabh

merch_ops,
The original question changed completely in the follow-up post, unfortunately!

Saurabh,
The problem is that the OP wants to enter 20915 in a cell and have *one* other cell which contains 209.25 but displays 209:15. The point we are trying to make is that you cannot do that with *one* cell. It's easy with two, as has already been shown.

Rory

Hi All:

Would there be some way to apply the concepts of the following to a desired range of cells [i.e. column B]?

I am stuck with 2000 and cannot test since this property is not recognized in 2000.

Reference:
http://msdn2.microsoft.com/en-us/library/aa214274(office.11).aspx

Bob

Sub ChangeSystemSeparators()
  Range("A1").Formula = "1,234,567.89"
  MsgBox "The system separators will now change."
  ' Define separators and apply.
  Application.DecimalSeparator = "-"
  Application.ThousandsSeparator = "-"
  Application.UseSystemSeparators = False
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:

Bob,
I don't think that would help as it's an application level setting, so it would affect all workbooks, rather than a range of cells. Also you would still be left with the issue of displaying the 25 as 15.
Regards,
Rory