I am trying to make the custom cell format change the display of a
number like 3.20 to 3:20
I tried ?0":" for a cell format but I cant get the decimal part to
work. I basically want it to look like a time format but not be one. If
I use mm:ss then it totally changes 3.20 into 4:48 Some time conversion
is applied.

0.":"_.00 was close. It shows 3.:20

I would appreciate any help.

Thanks
Scott

Hi Scott,

This can not be achieved thru Formatting only.
In Excel, times are stored as fractions of a day, and a day is 1 (the number 1 represents 1-1-1900).
That explains the way 3.2 is displayed: 3 days, 4 hours and 48 minutes, but you chose to only display the hours and minutes.
So if you want 3.2 to behave like time, Divide by 24 and by 60. It will give you 00:03:12
I don't know why you don't want the cell to be time. My experience is that attempts to build your own "time system" in Excel will
get you into deep trouble eventually.

Maybe you can explain what you are trying to achieve, so we can help you with a better solution.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



wrote in message ups.com...
|I am trying to make the custom cell format change the display of a
| number like 3.20 to 3:20
| I tried ?0":" for a cell format but I cant get the decimal part to
| work. I basically want it to look like a time format but not be one. If
| I use mm:ss then it totally changes 3.20 into 4:48 Some time conversion
| is applied.
|
| 0.":"_.00 was close. It shows 3.:20
|
| I would appreciate any help.
|
| Thanks
| Scott
|

Hi

=TIME(INT(A1),100*(A1-INT(A1));0)

Another way
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"00.00"),".",":"))


Arvi Laanemets


wrote in message
ups.com...
I am trying to make the custom cell format change the display of a
number like 3.20 to 3:20
I tried ?0":" for a cell format but I cant get the decimal part to
work. I basically want it to look like a time format but not be one. If
I use mm:ss then it totally changes 3.20 into 4:48 Some time conversion
is applied.

0.":"_.00 was close. It shows 3.:20

I would appreciate any help.

Thanks
Scott

Sorry!


=TIME(INT(A1),100*(A1-INT(A1)),0)



Arvi Laanemets

I have a time sheet that you enter times into and then it subtracts one
time from another and shows the seconds between each time. I want them
to be able to enter the time like 3.20 for 3 minutes and 20 seconds
instead of having them type in a colon each time to seperate the
minutes and seconds. My formula that subtracts the two "times" takes
the ingeger part and multiples times 60 and the decimal part gets added
as they are seconds.

Here is what some of my data looks like. Each time is the end of a step
that is preformed for each run.

Run #1 Run #2
Find part Time 1.25 2.55
Seconds 85 60
Load part Time 1.5 3.2
Seconds 25 25
Process part Time 1.55 3.25
Seconds 5 5


If you have any other ideas I would apprecaite to hear them.

Thanks Scott

Hi Scott,

Look he

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
|I have a time sheet that you enter times into and then it subtracts one
| time from another and shows the seconds between each time. I want them
| to be able to enter the time like 3.20 for 3 minutes and 20 seconds
| instead of having them type in a colon each time to seperate the
| minutes and seconds. My formula that subtracts the two "times" takes
| the ingeger part and multiples times 60 and the decimal part gets added
| as they are seconds.
|
| Here is what some of my data looks like. Each time is the end of a step
| that is preformed for each run.
|
| Run #1 Run #2
| Find part Time 1.25 2.55
| Seconds 85 60
| Load part Time 1.5 3.2
| Seconds 25 25
| Process part Time 1.55 3.25
| Seconds 5 5
|
|
| If you have any other ideas I would apprecaite to hear them.
|
| Thanks Scott
|

The VBA idea is neat but I think it would cause more confusion with the
people working with this spreadsheet. My intended audience that would
be using this spreadsheet are totally new to Excel. I think for them to
enter 320 for 3 min 20 seconds would be confusing. Entering 3.20, not
ideal, but a little bit more understandable. I was just trying to take
it the next step and make it "look" like a time entry of 3:20.

Thanks
Scott