Convert Text Time to Time

I have text in the following format:

800
830
900
930

How can I convert these text to 8:00 AM, 8:30 AM etc...

Thanks,

M

--
mdalb
-----------------------------------------------------------------------
mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=705
View this thread: http://www.excelforum.com/showthread.php?threadid=26248

0
9/22/2004 5:31:18 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
653 Views

Similar Articles

[PageSpeed] 15

Hi
try:
=INT(A1/100)/24 + MOD(A1,100)/(24*60)

>-----Original Message-----
>
>I have text in the following format:
>
>800
>830
>900
>930
>
>How can I convert these text to 8:00 AM, 8:30 AM etc...
>
>Thanks,
>
>MD
>
>
>-- 
>mdalby
>----------------------------------------------------------
--------------
>mdalby's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=7055
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=262480
>
>.
>
0
frank.kabel (11126)
9/22/2004 5:47:39 PM
One way 
Use this formula:

=IF(A1/100<12,LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)&"AM",LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)&"PM")

There may be an easier way, perhaps using special formats, but thi
should do the job for you

--
ElsiePO

-----------------------------------------------------------------------
ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390
View this thread: http://www.excelforum.com/showthread.php?threadid=26248

0
9/22/2004 5:52:06 PM
Reply:

Similar Artilces:

Time zone attribute
Hi, Is the time zone attribute an AD attribte or Exchagne attribute? Suppose, My mailbox is newly created in UK server & I'm at India. My client machine's time zone is set to india (new delhi, calcutta,mumbai). When i login to my mailbox for the fitst time (after it is created) from india, which has a time diff of 5:30 hrs. what would be my time zone on the server? Regards, Swamy Read Bharat Suneja blog it goes over this quite well. http://exchangepedia.com/blog/2007/02/dst-2007-understanding-what-needs-to-be.html James Chong (MVP) MCSE | M+, S+, MCTS, Security+ mse...

how to preserve paragraph breaks in pasted text?
When I paste text copied from a Word document into an Excel cell, Excel breaks the pasted text into separate cells according to the paragraph breaks contained in the pasted text. However, I would like to preserve those paragraph breaks and keep all the pasted text in the same cell. I realize that when entering text in a cell, I must insert Alt+Return to get a paragraph break to stay in the cell. But there doesn't seem to be a way to force pasted text to stay in a single cell when it contains paragraph breaks. Any advice would be most appreciated. In Word, change the hard returns ...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Text Area indicator Word 2007
When I installed MS Office, I set the word 2007 page to indicate the text area (doted lines) on 4 sides of the page. Wish to remove them now, but don't remember where it was that set them Help PLEASE TIA Word Options > Advanced > Show Document Content > Uncheck 'Show Text Boundaries" -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><&l...

Importing text from a website
Hello. Nursemaiding required for a perpetual lamer please. I want to put this stuff on an XL sheet or in another producer's spreadsheet if it will be easier. I wish to move the date over to the other side of the month and put the hour column in its own cells separate from the minutes as I wish to divide the hour (by 6) without touching the minutes. It must be a simple process I suppose but I wouldn't know where to start. Thank you in advance etc., etc.. http://aa.usno.navy.mil/cgi-bin/aa_moonphases.pl?year=2006 2006 Phases of the Moon Universal Time 00000 New Moon 000 First Q...

Search for text strings with multiple conditions
Hello! I want to search for the word "Hello" in columns which can consist of "Hello;Hi;Greetings" and want the hello must based on several conditions. Now I use this formula, but then I can=B4t find the cells which contains more text than just "Hello": =3DSUM(IF(BP33:BP1000=3DBP8;IF(BR33:BR$1000=3D"Hello";1;0))) I want the formula to be like this: =3DSUM(IF(BP33:BP1000=3DBP8;IF(BR33:BR$1000=3D"*Hello*";1;0))) But the "*Hello*" doesn=B4t work...does anyone know a formula which is very similar to the one above but can search throug...

trouble sending/forwarding mail in RTF or plain text via VPN
While using a VPN on a wireless network, Outlook functions fine EXCEPT that I cannot send/forward mail that is not in HTML format. When doing so, I only get the message that Outlook is searching for my corporate mail server. Other functions seem to work fine. Any clues?! Thanks-- ...

time out error downloading email from MSN to Outlook
when ever I have emails over 3 meg in size I get the error " a timeout occured while communicating with the server" You can see that it the download starts, I believe its a timing issue with http:// accounts. If I go to MSN 8 and look at the mail its fine, any ideas would be appreciated. Sometimes my Norton Antivirus chokes on large attachments, and the resultant delay processing the file times out the server. If you're running an AV program, try rebooting and resending with the AV disabled (scan the file manually first before saving, to be a good citizen). The problem m...

Outlook 2007 email sorting by time of day then date
I would like to sort all emails received by time of day 1st then by date. Haven't found an easy way to do this as the received time shows as all in one field ie (mm/dd/yy hh:mm:ss). Could get query builder to work for me. ...

alternate quoted text formatting?
Is there any way to change Outlook's behaviour vis-a-vi quoted text in replies. It uses vertical bars to indicate quote level, but these just don't show up (in any consistent way) when viewed in any non-Outlook mail client. I'd much prefer Outlook to use ">" for quoted text or at least some method that is not limited to viewing in Microsoft clients. The only way I can find to do this is to use the view all mail as plain text option, but then if I'm forwarded an e-mail from an Outlook user where there is already quoted text in the vertical bars style, these are jus...

How can I display a cell range in a text box in excel?
I want to display the contents of a cell range in a text box. I know how to put contents from one cell in a text box but I need to put the contents of 24 cells in my text box. Or how can I change the length and height of cells below other cells without changing the other cells length and height. Is there a way to put a line to freeze panes to change the length and height of cells below the line? Would the '&' work for this problem. eg if A1 = "Pat" and B1 = "Hughes" then the formula A1&B1 would return PatHughes, formula A1&" "&...

I would like to convert my dollar totals into a bar chart format
I am attempting this for the first time and would appreciate help. Thank you. EXCEL 2007 Hi Shirley, please go to:- http://www.pierrefondes.com/ Item number 108. Follow the intructions starting in cell K 1 to create a Bar Chart (it's very simple and only takes a minute or two). Then, change the underlying data in cells A 1 to B 10 to match your needs. This should give you what you want. If my comments have helped please hit Yes. Thanks. "shirleyweston" wrote: > I am attempting this for the first time and would appreciate help. Thank yo...

Save workbook as a Text file
Hi, I have recorded a macro to store an Excel workbook as a .* txt file. The result from the macro-recorder is mentioned below --------------------------------------------------------------------------------- ActiveWorkbook. SaveAs Filename:="C:\Temp\map1.txt", _ FileFormat:=xlTextMSDOS, CreateBackup:=False --------------------------------------------------------------------------------- If I carry out above action manually the text is taken over exactly the same as in the workbook, however if I carry out the action by using the above a macro, some data with "quotations ma...

transferring data/text to another worksheet
I have two columns/rows with the following data on Sheet1: Column: A B Row: 1 178 buy 2 buy 3 185 buy 4 140 sell 5 sell 6 sell 7 130 buy I want to tranfer to sheet 2, all the cells in column A that have number value and if column A has a number, then transfer the text i column B. This is what I want sheet 2 to look like: ...

track changes text shows on screen does not print
tracked changes (of an imported excel graphic) shows on screen in final view but does not print. I accepted the changes and it still shows on screen but does not print. (1) In your Print dialog, you probably have a "Print What?" menu. Change it to "Document with Markup." (2) If you've Accepted the Changes, they should not still show on screen. Are they changes made in Excel that need to be Accepted in Ecxel? On Mar 31, 12:42=A0pm, Hyphenation <Hyphenat...@discussions.microsoft.com> wrote: > tracked changes (of an imported excel graphic) shows ...

Searching sent mail by time values
Can I create a search folder which would search for all email sent in bettween two date and time values e.g (UK date and time) between 01/11/09 and 30/11/09 if between 17:00 and 07:00 TIA Trevor No, since the time is part of the date in a single field (sent). -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Trevor Aiston" <TrevorAiston@discussions.microsoft.com> wrote in...

Printing Text Boxes with a chart
Hi - sorry if this is a no-brainer but I can't get it to work I make a chart from figures on a worksheet - easy Then I add some text boxes on top of the chart to comment on specific features etc Then I go print and select "selected chart" - the printer prints the chart but not the text boxes on top of it How can I print the chart with the text boxes caontaining comments Thank DR Dave - When you select the chart, do the text boxes vanish? That's because they are not part of the chart. Select the chart first, then add your text box. In fact, just select the chart and star...

Convert Quicken 2006 to MS Money 2006 Trial version
I am currently using Quicken Premier 2006. I downloaded the trial version of MS Money 2006. However, it states that you can only convert data from Q2005 and earlier. When I tried to convert Q2006 into Money, it wouldn't do it, saying it was unable to convert from this version. Any resolutions to this? I would really like to try the trial version of Money. Not converting the data doesn't prevent you from using the Money trial. You'll just have to start from scratch--presumably you had to do this with Quicken as well. If the past predicts the future, M07 will convert Q06...

Can you set the time interval before ControlTipText is displayed?
Hi there, I am implementing the "hack" of placing the selected value of a list box into its ControlTipText in order for long values to be displayed when necessary as my list box cannot get any bigger: Private Sub lbxDisplay_DblClick(Cancel As Integer) Fill_Display_ControlTipText End Sub Private Sub Fill_Display_ControlTipText() Dim ctlDisplay As Control, var As Variant Set ctlDisplay = Me.lbxDisplay Debug.Print ctlDisplay.ItemsSelected.Count For Each var In ctlDisplay.ItemsSelected Debug.Print ctlDisplay.ItemData(var) Me.lbxDisplay.ControlTipTex...

Publisher 2000 & web pages & text on bmp's
I have a web site for my software I sell. A couple pages contain BMP pics of a few screens & a couple reports. When I am in the design mode the text on these bmp's is relatively clear but when I PREVIEW the pages with these bmp, the text is almost illegible. I have tried to insert various objects but with NO LUCK. Can anybody offer me some suggestions of what to do? Thanks. DeWayne to auto reply remove the x from my address below dchiasxs1@rogers.com web pages support gif or jpg formats. Pub would convert the bmp to gif which could effect resolution. I recommend you create your im...

Convert data in Excel file to vsd format
H We are dynamically building an Excel sheet aith data to be shown as an Organization chart How can we convert this file into vsd format from Visual Basic Also, how can we show the Excel file contents on the WEB using asp Really Urgent. Regard Ajith well, I'm looking at Office2003 and Visio2003. Visio has the OrgChart Wizard for import of excel data, and Excel has the export of html. I believe this is functional back to the 2000 version as well. What version of the tools are you using? Al "Ajitha Anil" <anonymous@discussions.microsoft.com> wrote in ...

How to convert two dates in to HH:MM:SS
Hi Guys, I am trying to calculates the two dates cells diffrence int hour:minutes:seconds. could some help me in finding a solution i Excel sheet. eg. Cell A1 = Jan-01-2004 00:00 Cell B1 = Jan-02-2004 23:30 Cell C1 = 47:30 looking for a result like 47 hours 3 min. thanks sa -- sashaik ----------------------------------------------------------------------- sashaikh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555 View this thread: http://www.excelforum.com/showthread.php?threadid=27118 Subtract the two cells and use the Custom Format ...

Point labels in an Excel scatter plot to be associated text
I am trying to plot a scatter plot where I would like each point to have a specific name. For instance, I have three columns, company name, total revenue, and % margin. I would like to plot revenu vs. % margin and when clicking on a specific point, I would like to find out the company name. Check out Rob Bovey's Chartlabeler at www.appspro.com or John Walkenbach's Chart Tools at www.j-walk.com. If you want to see the labels only when you hover the mouse over a point see Hover Chart Label http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html Note that the add-...

Time Shift effieceincy data
New to excel but have been presented with this project. Any and all help sincerely appreciated. 8 hr shift efficiency data Shift time 8 hrs. Need to document time spent moving, on break/lunch, paper movement 0800 on duty 0845 starts initial shift 0900 movement for 45” 0945 stops movement for break 1000 start again 1045 break 1100 movement for 1 hr 1200 lunch 1300 movement 1400 movement until 1430 1430 complete paper movement until 1500 1500 off duty Thanks would like to put in a chart for productivity -- John ...

New Bi-weekly Time Clock Crystal Report
I have a new and improved Timecard report that uses Crystal Report Writer. Just putting in finishing touches. Will calculate Regular Hours, Ovetime Hours & Holiday Hours for employees working on the actual Holiday. Will also new round hours to the nearest 1/4 hour. ...