Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 7th 06, 06:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 18
Default Custom Cell format to mimic time format

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


  #2   Report Post  
Old November 7th 06, 07:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default Custom Cell format to mimic time format

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
|


  #3   Report Post  
Old November 7th 06, 07:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 99
Default Custom Cell format to mimic time format

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



  #4   Report Post  
Old November 7th 06, 07:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 99
Default Custom Cell format to mimic time format

Sorry!


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



Arvi Laanemets


  #5   Report Post  
Old November 7th 06, 07:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 18
Default Custom Cell format to mimic time format

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



  #6   Report Post  
Old November 7th 06, 08:29 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default Custom Cell format to mimic time format

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
|


  #7   Report Post  
Old November 7th 06, 09:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 18
Default Custom Cell format to mimic time format

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Format Cell nastech Excel Discussion (Misc queries) 13 April 28th 06 08:31 PM
Reference Cell in custom format???? lil_ern63 Excel Discussion (Misc queries) 3 September 1st 05 02:27 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Cell will not format numbers correctly for a 13 digit custom barc. Laudan Excel Worksheet Functions 4 April 11th 05 08:13 PM
How do I format a cell for a custom part number? PJ Excel Discussion (Misc queries) 4 March 3rd 05 03:57 AM


All times are GMT +1. The time now is 03:07 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017