Formatting Times and converting to text

Hi,

I have got a spreadsheet which has a column which holds a time calculated 
from another column. The times in both columns are in the format m:ss.000. 
The sheet when complete will be imported into Access which unfortunately 
xan't hold times with seconds with 3 decimal places so the recomended 
solution is to hold the time in a text field.

So how in Excel can I convert the times into a text field without losing the 
currently displayed value, as whatever I try changes the value to the offset 
value (i.e. 7.88E-05 instead of 0:06.815).

Thanks

Graham 

0
spam3102 (2)
12/22/2008 2:10:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
311 Views

Similar Articles

[PageSpeed] 1

Do it this way:

=3DTEXT(A1,"m:ss.000")

Hope this helps.

Pete

On Dec 22, 2:10=A0pm, "Graham Naylor" <s...@spam2.co> wrote:
> Hi,
>
> I have got a spreadsheet which has a column which holds a time calculated
> from another column. The times in both columns are in the format m:ss.000=
..
> The sheet when complete will be imported into Access which unfortunately
> xan't hold times with seconds with 3 decimal places so the recomended
> solution is to hold the time in a text field.
>
> So how in Excel can I convert the times into a text field without losing =
the
> currently displayed value, as whatever I try changes the value to the off=
set
> value (i.e. 7.88E-05 instead of 0:06.815).
>
> Thanks
>
> Graham

0
pashurst (2576)
12/22/2008 2:30:21 PM
Hi Graham,

=TEXT(A1,"m:ss:000")

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Graham Naylor" <spam@spam2.co> wrote in message 
news:wZadnb-E1JFEAtLUnZ2dnUVZ8rKdnZ2d@bt.com...
> Hi,
>
> I have got a spreadsheet which has a column which holds a time calculated 
> from another column. The times in both columns are in the format m:ss.000. 
> The sheet when complete will be imported into Access which unfortunately 
> xan't hold times with seconds with 3 decimal places so the recomended 
> solution is to hold the time in a text field.
>
> So how in Excel can I convert the times into a text field without losing 
> the currently displayed value, as whatever I try changes the value to the 
> offset value (i.e. 7.88E-05 instead of 0:06.815).
>
> Thanks
>
> Graham 

0
nicolaus (2022)
12/22/2008 2:31:24 PM
Thanks Guys, perfect solution as always.

Graham


"Graham Naylor" <spam@spam2.co> wrote in message 
news:wZadnb-E1JFEAtLUnZ2dnUVZ8rKdnZ2d@bt.com...
> Hi,
>
> I have got a spreadsheet which has a column which holds a time calculated 
> from another column. The times in both columns are in the format m:ss.000. 
> The sheet when complete will be imported into Access which unfortunately 
> xan't hold times with seconds with 3 decimal places so the recomended 
> solution is to hold the time in a text field.
>
> So how in Excel can I convert the times into a text field without losing 
> the currently displayed value, as whatever I try changes the value to the 
> offset value (i.e. 7.88E-05 instead of 0:06.815).
>
> Thanks
>
> Graham 

0
spam3102 (2)
12/22/2008 3:20:03 PM
Reply:

Similar Artilces:

Nested If Function to be converted into a user-defined custom function
Dear Experts: below formula (nested if formula) WORKS FINE in EXCEL 2007, but it is not working in 2003 for known reasons (number of conditions exceeding the limit) . I now would like to create a user-defined custom function (UDF) in Excel 2003. I know how to operate the VBA Editor but I got no idea how this nested If-Formula translates into a VBA-code. Could somebody please help me. Help is much appreciated. Thank you very much in advance. Regards, Andreas ------------------------------------------------------------------------------ Below formula (nested IF-Function...

Quicken Convert #7
Downloaded Money05 trial. Validated 10 MB quicken file. Followed instructions in Money for conversion, hung-up computer, twice. Reduced size of quicken file to 5 MB. Now receive following error message: ------------------------------------------------------------ Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing other programs and making sure the disk you are copying your file to has enough space. Then try converting the file again. --------------------------------------------------------...

Convert MFC App to MFC ActiveX Control?
Dear there, How to Convert MFC App to MFC ActiveX Control? I had a MFC MDI application which is also a COM server. Now there is a need to turn this into an OCX control so that it can be used in a dialog. Your help will be highly appreciated. Jasen You could try with http://www.microsoft.com/mind/0497/mfc.asp (we used it at work, it works fine for quite some years now). Dear Mr. Pusic, Thank you very much for the information. I will spend time on the paper. Jasen "Goran Pusic" wrote: > You could try with http://www.microsoft.com/mind/0497/mfc.asp (we used it at > wo...

Time Stamp on Inbound Mail
Is there a way to time stamp emails when they ARRIVE, vice when they are sent. Because of our SLA, we have certain timeframes to get work done in and the clock on that starts when they arrive at our facility. But, since it's time stamped with the time the user/client sent it, we are behind - as sometimes their emails don't arrive within the reasonable amount of time from when they sent it Any assistance would be greatly appreciated. A configuration, a 3rd party software, etc....... We are running Exch 2k3 & OL 2k3 Thanks Mack Outlook messages do contain a time stamp for the ti...

Add Word and change format
1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the faster way in case I got thousand of codes? 2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc. Now I would like to make it to be standard to 4 digit for the middle number to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above, I got more than thousand of such codes... What's the faster way? Kelvin The first could be done wi...

Conditional formating of Charts
Hi, I have a scatter chart and need to conditionally format points and associated labels for each point. The condition is not related to the data series to which the chart is linked. Let me know if there is any specific VBA code for doing the same. You can reach me at krishna.guha@citigroup.com There are some non-VBA suggestions here: http://peltiertech.com/Excel/Charts/ConditionalChart1.html To change some points of a series or some series in a chart based on a known criterion, you can adapt one of the macros in this post: http://www.google.com/groups?selm=3F4B8D49.3080508%40yah...

Time formats
I am creating a template which requires the user enter a time (not present time, flight times), for example the user will be listing a number of flight arrivals and departures and what I want to do is have the user simply enter something like 804p and have the cell display it as 8:04 pm. I have looked at some custom formats and nothing seems to be able to do this. Any help would be appreciated. Thanks! Tracey ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ See http:...

zoom on RTF text
Hi all, I am currently developing a windowless textbox object using ITextServices. Everything went very well until performed zoom on text. In my code the RTF text is rendered using TxDraw, which calculates zoom factor as this: zoom factor = (pixel height of client rect)*2540/[Himetric vertical extent]*[pixel per vertical inch]. But the result wasn't really that good, the scaled text often went out of box, and word wrap wasn't consistent specially when it scaled below 60%(above 100% much better). Does anybody know what problems caused it? Any input would be appreciated. Thanks. jm H...

How to join date/time field when "days" are same but "time" differ
Good evening everyone, I have one TRANSACTION table that contains: 1."date/time" field (YYMMDD hh:mm:ss) 2."quantity field" And another PRICE table that contans: 1."date/time" field (YYMMDD hh:mm:ss) 2."price field" Now, I want to join the "date/time" fields so I can perform a simple "quantity * price" calculation on each transaction-row. But the time differes (hh:mm:ss) and I just can't find a way to make Access accept a relation between the days (it outputs nothing). How can I make it ignore difference in the "ti...

Text to dates
I have a column of dates F6:F148, the dates have been entered in the following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" & "21 Jan 2009". I'd like to be able to change them all into a common date format 21 Jan 09 (I'll also change the sheet so that data is validated to this date format) Does anyone know a way I can do this? Thanks You can use the 'Convert Text to Columns Wizard to convert the dates --Select the range of dates which needs to be corrected. --From menu Data>'Text to Col...

How to convert a Chinese registry value from within a US code page?
First of all, our Win32 MFC VC++ app is not written in Unicode as it should have been. Given that - it reads uninstall information from the windows Uninstall registry tree and writes it to a report. It writes out the displayname of the application. Normally this is not a problem, but when it encounters a Chinese application name, it displays the name as question marks. I assume this is because the string is in Unicode in the registry, but our app is not Unicode so it can't represent the characters. Unfortunately for the moment, converting this very large app to Unicode is not an o...

Cannot select a range of text
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am unable to select a range of text with either the track pad or my mouse. I am able to double- or triple-click to select, and can use keyboard commands to select all, etc. <br><br>This is a new problem that has not existed before. <br><br>Thanks! <br><br>Art Hi Arthur: Not enough information to answer. Please carry out some diagnostic testing for us: 1) Is this one document, several documents, or all documents? 2) Is this both .doc and .docx? If not, which ...

automatic formatting
i use microsoft outlook calendar for all our daily appointments. i do not want to have the highlight feature. when i ask the assistant, i do as it says, but i cannot click the automatice formatting (it is gray and will not allow access) how do I get automatic formatting so that I can get rid of the highlighting and add color thank you ...

How do I change the text size in a drop down box
I am using excel 2003. It seems to default to 10pts. Changing the size in the source list or the default in the general tab did not change the size. ...

choice to colour text
Hi, hope someone can help. I have a shared spreadsheet with quite a lot of users. I need to categorise who is using the spreadsheet so that text that they add comes up in a different colour. Is there anyway to do this? I realise they could just change the text colour themselves, but it would be simpler in the long run if when they tried to input text they were faced with a question, and the answer changed the text colour automatically. Any help is much appreciated! Nick Hi, Maybe a user has to log in to the worksheet. eg when the sheet opens, an input box asks for username. The a...

How do I put Excel data into a US map format?
I want to feed Excel data about population and trends into a map format instead of a bar graph or pie chart. Is there a plug-in or some such thing that I can use that works with Excel? Ultimately, I want to have each state depicted by a color code for a range of population or an amount of certain data. I am using Excel 2002 in a Windows XP environment. ...

Conditional Formatting #N/A to show white
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> XL2000 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I have several formulas that show #N/A until a value is present <br>&nbsp; I tried Conditional Formatting to turn that cell white until a value is inserted <br>but I don't know what I am doing..</html> This is a multi-part message in MIME format. ------=_NextPart_000_046B_01C43483.49C9D260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Formula=20 =3DIS...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...

Problem with Text box and Background
I am using the background as a temple for a form I add test boxes where I want to have text typed in The problem is when I print the page the text I have typed in isn't where it is supposed to be It is below the line of the background form How do I solve this problem? Web page? Best asked in the publisher.webdesign group -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "News" <gmhull@sbcglobal.net> wrote in message news:pOKFf.27487$F_3.19174@newssvr29.news.prodigy.net... >I am using the background as a t...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

Dialog text only updated on mouse moves
My PreTranslateMessage() function has code in it to update some of the text in one of my dialog boxes. It works, but instead of updating it every second, it only udpates the text when my mouse is moving or clicking. Any ideas on what might be causing this? Thank you! Well, that's becuase PreTranslateMessage gets called only when there is message in the queue for that window. Instead use a timer. See OnTimer (WM_TIMER) AliR. <PloutzMR@Npt.NUWC.Navy.Mil> wrote in message news:1154456910.215194.170930@s13g2000cwa.googlegroups.com... > My PreTranslateMessage() function has c...

text boxes that label axes in line/scatter graphs
I tried to use "SAT-M" as y-axis label, but "M" was cut off (actually went to second line that could not be seen). I tried every trick I know to lengthen the text box, to fit label on one line. Hi, The only way to length chart textboxes is with more text. But at some point more text will cause the content to wrap. The charts textboxes do not have the ability to be sized manually even though the bounding box and grab handles would suggest otherwise. Try adding a few spaces or even non-breaking spaces ALT+0160 (Numpad) Cheers Andy Peg wrote: > I tried to use &qu...

graphing counted time stamps
Is it possible to generate a graph of counted timestamps? I am importing into Excel a log file and get one column of time stamps, some time stamps are 1 time , or multiple times. The idea is that for each one time stamp on the x-axis the y axis shows the counted amount of timestamps for this time. I assume this should be a common problem, but I could not find the answer for this. Any help appreciated, Wolfgang Create a PivotTable and PivotChart with the timestamp as the row field and the data field. Make sure Excel uses COUNT(Timestamp) as the statistic it shows and not SUM(,,,...

Excel worksheet programming for time restrictions.
I have several cells (properties) that will be visible or not depending on the time of day. I am using VBA to set the value of the properties and need a way to use not just the TIME function to establish the system time but, a way to test for a period of time, for example the DAY SHIFT, SWING SHIFT AND GRAVEYARD SHIFT. Using If Then Else conditional statements to test the system time to a span of time to indicate the shifts aforesaid. What I am not clear on is the syntax in VBA to use when identifying the shifts and testing for conditions met. HElP? Is this any help? Dim tim0800 Dim ...

Multiplying time by $ per hour
Hi, How can you multiply 58:41:00 times $200 per hour? I guess you have to change the 58:41 into an integer and decimal format but I don't know how to do that? Also, if you have a long list of times, what is the best way to check the addition of those times? Is there a quick way to add the minutes and the hours separately or turn it all to minutes and then divide by 60? An hour is 1/24th of a day. Try =R6*24*200 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Alan" <alancalan@excite.com> wrote in message news:u5i8n2lb0hl01aui0js99pc0bftsgjmn56@4ax.com.....