Formatting times

How do I format a column so that it shows
hours:minutes:seconds:tenths
when either hours or tenths may not be part of the data input?
I'd also prefer not to have zero for the hours or tenths if they're not
there.

And why does the "input field" at the top of the screen show the number as a 
time even if I've formatted the cell as one of the other "time" choices?
TIA
bj



0
bjones44 (68)
7/28/2009 4:14:51 PM
excel 39879 articles. 2 followers. Follow

2 Replies
820 Views

Similar Articles

[PageSpeed] 6

That going to be hard using the Format Cells dialog box but suppose your 
times are starting in A1 then the following formula will do what you want:

=TEXT(A1,IF(A1<1/24,"mm:ss.0",IF(ROUND(MOD(A1,1/86400),7)=0,"[h]:mm.ss","[h]:mm:ss.0")))

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"bjm" wrote:

> How do I format a column so that it shows
> hours:minutes:seconds:tenths
> when either hours or tenths may not be part of the data input?
> I'd also prefer not to have zero for the hours or tenths if they're not
> there.
> 
> And why does the "input field" at the top of the screen show the number as a 
> time even if I've formatted the cell as one of the other "time" choices?
> TIA
> bj
> 
> 
> 
> 
0
7/29/2009 6:21:01 AM
Thanks for the suggestion, I'll give it a try next time I dive into that 
project.
bj
(I'm on Usenet, I don't have a yes button)

"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:8B2B5275-5A2F-47CB-8F9B-B4AB6C077FF7@microsoft.com...
> That going to be hard using the Format Cells dialog box but suppose your
> times are starting in A1 then the following formula will do what you want:
>
> =TEXT(A1,IF(A1<1/24,"mm:ss.0",IF(ROUND(MOD(A1,1/86400),7)=0,"[h]:mm.ss","[h]:mm:ss.0")))
>
> -- 
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "bjm" wrote:
>
>> How do I format a column so that it shows
>> hours:minutes:seconds:tenths
>> when either hours or tenths may not be part of the data input?
>> I'd also prefer not to have zero for the hours or tenths if they're not
>> there.
>>
>> And why does the "input field" at the top of the screen show the number 
>> as a
>> time even if I've formatted the cell as one of the other "time" choices?
>> TIA
>> bj
>>
>>
>>
>> 


0
bjones44 (68)
7/30/2009 2:01:00 AM
Reply:

Similar Artilces:

Using Logical OR on text in Conditional Formatting
I have a column with various text entries. I have created a conditional formatting rule for each type of entry but the formatting can be grouped together, so I would like a Conditional Formatting rule that says: If string contains "foo" or "bar" then colour red If string does not contain "yibble" or "wibble" then make bold etc. Any idea how I can do this rather thasn having to manage over 30 rules with each one looking for a single text entry type. Thanks Darius Try a formula of =OR(A2="foo",A2="bar") =AND(A2<>"y...

Average delivery time
Hi everybody Performance measures on our exchange server showed that the average delivery time counter has values between 100'000 and 1'000'000 of tens of milliseconds. I already saw values of 13'307'076 of tens of milliseconds! Even a database which has no mailboxes on it shows a constant value of 21'440 over a period of the last 6 hours. Technet said that this could indicate a performance problem with the MTA. How can I verify that or even better solve it? Any hints? Exchange Server 2003 - SP2 Regards Peter ...

Outlook 2007: Can't accept proposed meeting times?
Does anyone know anything about this in 2007? Is it a bug or am I missing something? When I send an meeting request and the other receipient proposes a new time, I get a response back but there is no "accept" button. I have to manually update the meeting time. Setup: - both ends are OL2007 - both ends uses pst files - no exchange server involved - both ends connect to an IMAP server TIA 1001001100100 1001001100100 <ANonnyMoose@no-email.org> wrote: > Does anyone know anything about this in 2007? Is it a bug or am I > missing something? > > When I send an meeti...

Open XLS file without it's number format using VBA?
An XLS file has been saved with it's numbers formatted to a specified number of decimal places. Using VBA how do you read in that XLS file with the numbers unformatted and with the original number of decimal places? Workbooks.Open (filename:="myFileName.xls", ????) Not sure what you mean by "read in". The values are still in the cells. If you open the workbook, then transfer the cell values from the worksheet to VBA, you'll get the entire number, not the version that is rounded for viewing purposes. On Wed, 06 Oct 2004 22:55:55 GMT, Dennis@NoSpam.com wrote: >...

Send Out of Office more than one time
Hi! Is it possible to send more than one Out of Office message to the same person? Im using Exchange 2003. Regards, Mikael Johem On Thu, 13 Oct 2005 09:55:04 -0700, Mikael Johem <MikaelJohem@discussions.microsoft.com> wrote: >Hi! > >Is it possible to send more than one Out of Office message to the same person? >Im using Exchange 2003. > > >Regards, > >Mikael Johem What is the goal? Sending an OOF once is a good preventative measure against mail loops. On Thu, 13 Oct 2005 09:55:04 -0700, Mikael Johem <MikaelJohem@discussions.microsoft.com> wrote: ...

Outlook 2003 shows "Enter network password" every time I hit send/receive
OL 2003 seems to have a bug. I have a pop account which it can't remember the password for, no matter what I do. Have tried giving correct permission in registry, and have re-created the mail profile. Nothing helps. Any ideas? /M I seem to be having the same problem. I haven't tried the procedure MS posted about resetting thre registry. I'm lost also. >-----Original Message----- >OL 2003 seems to have a bug. I have a pop account which it can't remember >the password for, no matter what I do. Have tried giving correct permission >in registry, and have re-c...

Conditional Formating and SetFocus Question
Thanks in advace. This place has answered a lot of questions before I needed to ask. Now, I need to ask. I have a Continous Form [DetailSubForm] based on table [DetailTbl] that has the following fields. *[DetailID], [DocID], [CompDate],[SubDate], [ConfirmDate], and [ExpireDate]. The [DocID] field is a dropdown list that draws the data from the [TrainDocTbl]. I would like to use GetFocus to go to one of the Date fields depending on the selection made in [DocID]. It would also be nice to be able to declare the backcolor and forecolor of the whole record depending on that choice...

0100 (24 hour) time
Hi, I wish to type in the cells 0100, 0200, 0300 etc but cannot see the format type that retains these numbers as four digits (allows me to go to 2400) . Which format is it - I have tried several and it drops the first zero(0) off. Cheers Mike You need to custom format the col as: hh:mm then enter the times with a colon, eg: 00:00 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MikeR-Oz" wrote: > I wish to type in the cells 0100, 0200, 0300 etc but cannot see the format > type that retains these numbers as four digits (allows me to go to 2400) ....

Adding options to "Show Time As" in Calendar appointment
What is the simplest way to add a few options to this dropdown, like "Sick" etc... I assume this would need replacement of the default form for this globally - how easy is this? Thanks J John wrote: > What is the simplest way to add a few options to this dropdown, like > "Sick" etc... > > I assume this would need replacement of the default form for this globally > - how easy is this? > > Thanks > J I'm not an Outlook forms expert, but I think a custom form with some behind-the-scenes code will do what you need. However, it's g...

Contact View and Automatic Formatting
I have installed Outlook XP (Office XP have SP1 and SP2); I try to personalize a View of Contact folder. I want to use Automatic formatting and set this condition: "Message Class" contains "IPM.Contact". It doesn't work! When i used Office 2000 it work.. strange.. any helps?? Andrea ...

Format this cell like that cell, automatically?
I keep my grades in an Excel workbook of four sheets, of which the two relevant ones are Attendance and Grades. During the semester, if a student drops the course I mark all cells for that student in both worksheets as Locked and I apply a gray pattern to them. Having to do this in two worksheets is kind of a nuisance. Is there any way to set, say cells Grades!A11:AP11 to pick up automatically any formatting and Lock/Unlock status as it changes in Attendance!A1? I know I could do this in a macro, but I'm wondering if there's any purely worksheet way to do it. -- Stan Brown, O...

Conditional formatting based on matching record in separate query
I would like to conditionally format a patient's name (e.g., Bold) in a report, based on whether it finds that name in a separate query of New Admissions. How can I make my condition look for matching data in the query? The table name is Today'sList, and the query name is Admissions. The matching field name containing the patient's name is "Field1". Do I need to construct an expression using IIf (I'm assuming)? My question is, somehow I need my conditional expression to say "If Field1 matches any record's Field1 in this other query (Admissions),...

Printing Drafts emails Shows a "Sent time and Date"
Hi we have recently upgraded one of our clients to windows 7 / Office 2007. One of the users have reported that when printing draft emails there is a sent time and date that gets added to the top of the email even though the message has not been sent. I have looked through the outlook settings but cannot seem to find a way of turning this off. Any help on this would be appreciated. I have test the same on my own machine which is vista SP2 with office 2007 but get the same issue. I have noticed a date and time being put on draft emails, this started approximately 3 weeks ago. Co...

GP 10: Check printing format problems
When printing a check from the Payables Transaction Entry window, the check does not print with the same alignment from different workstations, eventhough the same printer and check format is used on all workstations. By alignment I mean the pay to name, check date, and check numerical amount are in different positions. Would a different operating system be the cause of this, some have XP others have Vista or would there be another reason for this? Yes, absolutely - a different printer driver can cause this. However, usually it will be a slight difference in alignment, so if they are ...

Conditional formatting based on an equal value in another workshee
I have a large spreadsheet with part numbers and prices. Have a second worksheet with part numbers and special prices. I want to have the first sheet cell to format bold/colur if the part number appears in the special list in the second sheet which is dynamic. Parts are being added or removed over time. Both partnumbers are in column B. -- Jack Create a defined name (Insert>Name>Define...) for the list in the other sheet, then use a formula of =ISNUMBER(MATCH(A2,the_list,0)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) &q...

Number format code
I can't seem to figure this one 21 1/2" vs. 21 " I am trying to create a custom format number code showing the inch hash marks after the number. My problem is when a whole number is input the space between the number and hash marks are annoyingly too far apart. Any help would be greatly appreciated. You could use conditional formatting using the a formula such as: =INT(A1)=A1 custom format as #\" and =INT(A1)<>A1 custom format as # ?/?\" 21 will be formatted as 21" and 21.5 as 21 1/2" Tyro "Woodbutche...

Plotting elapsed time when calculated
Hi folks I have a x-y scatter graph with a log scale y-axis. I recently had to change the x source data, now the data does not plot correctly. To give you an idea, the y-axis is percentage (3 cycles of log going up to a maximum of 1000) and the x-axis is elapsed time in minutes going from 0-240. I have 6 data points plotted. Before I made the recent change the x-axis coordinates (elapsed time values) were just a column of numbers (in Cells C36:C41), formatted as Number. Rather than enter the (elapsed) time directly, I now want Excel to calculate it (Time A - Time B) and use the result as the ...

time and date charting
Whats the best way to chart date/time and percent values from say 7/12/06 until whenever. I want the chart to autoupdate as the data is entered? Ed -- changetires ------------------------------------------------------------------------ changetires's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35414 View this thread: http://www.excelforum.com/showthread.php?threadid=569922 ...

HTML Format of Comments Indicator
Is there a way to change how the HTML comment indicator shows on the HTML file? (Without manually editing the HTML file) What happens now in that every comment is sequentially labelled [1], [2], [3] and so on. Is there a way to just change it to an asterisk? Or another character? I briefly looked into the Script Editor but couldn't figure it out. Thanks! Greg ...

Help in programmatically setting formatting in an AppointmentItem
Hi, i have created a macro to generate some meeting invites. I used the WordEditor to set some formatting (Bold on/off, hyperlinks etc). However, i find that the formatting works only partially (or not at all). so far i've noticed that only the first invite generated is perfectly formatted. Can anyone help me out with this? Do i need to add something to the code or set some objects to null to ensure that the formatting stays throughout my outlook session? More info, please: Outlook version, relevant code snippet, more explanation of what you mean by "the first invite...

Date format problem #10
Hello I am having great difficulty in converting American date format int dd/mm/yy. I have used both the date format option and also the custo option, this has not been sucessful. I have attached a small dat sample and I would greatly appreciate if someone could provide som assistance in converting these dates. The dates are exported from an ERP system which operates the julia date system...unsure if this is the reason why I can't sucessfull convert all dates???! regards A Attachment filename: date format.xls Download attachment: http://www.excelforum.com/...

Conditional Formatting
Using Excel 2007, I have dollar values in columns A, B and C. C=B-A. I would like to highlight the cells in column C red if the cell value is greater than 5% of the value in column A. Here is my formula, can someone please tell me what I'm doing wrong? The formatting highlights every cell, regardless of it's value. =$C$2>($A$2*0.05) Get rid of your absolute references (the $'s). Then try it again. HTH. "sbres" wrote: > Using Excel 2007, I have dollar values in columns A, B and C. C=B-A. I > would like to highlight the cells in col...

Problem formatting date as "dd/mm/yyyy"
Hi, I am formatting cells using the following code: sws1 is the source sheet and dws is the destination sheet. dws.Cells(datarow, 3) = Format(sws1.Cells(j, 6), "dd/mm/yyyy") The value of "31-03-2010" in sws1 is displayed as "31-03-2010" in dws and not as "31/03/2010" as desired. The cell in dws is formatted as general The value of "12-03-2010" in sws1 is displayed as "03-12-10" in dws and not as "12/03/2010" The cell in dws is formatted as date. The dates in the sws1 sheet are formatted as dates Pleas...

Formating cells
Hi, need a help with cell formating. Would like to format cells so user cannot input more than 10 caracters into a cell and than 4 rows with 15 caracters into each cell possible input. Is this easy to do with excel? Thanks all BT Hi, Yes, use data validation (Data/Validation). Start by selecting the cells with the same restrictions (10 characters) then go to Data/Validation, select "Custom" from the drop down menu and type =LEN(A1)<=10 (where A1 is the active cell) , next click on the "Error Alert" tab and leave a message stating that 10 is the maximum characte...

Text in custom format not showing up in cell results ... (?)
Wonder what I'm doing wrong this time ... ? <g> I've used this technique so many times before but today it isn't working. In cell B2 I have this formula: =(B1*16) The custom format for this cell is, instead of just number: @" units" All I get in B2 is a number, 32 in this case, but not " units" after it. What am I missing pls? Thanks! :oD ###" units" -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "StargateFan" <IDon'tAcceptSpam@NoJunkMail.com> wrote in message news:4bhs35pbh5ljrlnipif...