convert text into date

can any one help me 
i have data in excel like 436,or 1851 i wanted to convert this text format 
into time and date format so actully i wanted it to be as 4:36 AM or 18:51 PM,
I found one solution if you manually put ":" between "4" and "36" then 
format cell as time, this will work but i have a lot of data, is there any 
formula to put ":" between charactors 
thanx
shabbir 

0
arshab1 (1)
10/9/2004 4:29:08 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
596 Views

Similar Articles

[PageSpeed] 37

Hi
in adjancent cell try the following formula
=TIME(LEFT(A1,1+(LEN(A1)=4)),RIGHT(A1,2),0)
and format this cell as time

--
Regards
Frank Kabel
Frankfurt, Germany

"arshab1" <arshab1@discussions.microsoft.com> schrieb im Newsbeitrag
news:5BA83232-3639-4DF6-A858-68BE287E9219@microsoft.com...
> can any one help me
> i have data in excel like 436,or 1851 i wanted to convert this text
format
> into time and date format so actully i wanted it to be as 4:36 AM or
18:51 PM,
> I found one solution if you manually put ":" between "4" and "36"
then
> format cell as time, this will work but i have a lot of data, is
there any
> formula to put ":" between charactors
> thanx
> shabbir
>

0
frank.kabel (11126)
10/9/2004 4:36:55 PM
In addition to Frank's method, another way to determine the values for the
hour and minute is like this:

  =TIME(INT(A1/100),MOD(A1,100),0)

On Sat, 9 Oct 2004 09:29:08 -0700, "arshab1"
<arshab1@discussions.microsoft.com> wrote:

>can any one help me 
>i have data in excel like 436,or 1851 i wanted to convert this text format 
>into time and date format so actully i wanted it to be as 4:36 AM or 18:51
PM,
>I found one solution if you manually put ":" between "4" and "36" then 
>format cell as time, this will work but i have a lot of data, is there any 
>formula to put ":" between charactors 
>thanx
>shabbir 

0
anonymous (74722)
10/9/2004 5:34:41 PM
Reply:

Similar Artilces:

Is Word Converter Compatible with Server 2008??????
We have tried to install the word converter (so 2003 word can read and save 2008 documents). When you try to open a 2007 document, we get the error " There was an error opening file" We have tried on a production server and also test server had have the same results. Uninstall and reinstall several times and . Run as Administrator, everything but no luck. Everything is up-to-date with the lates service packs. We would install Office 2007, but the clients app that does merge to word is not compatible with Word 2007. Is the word converter compatible with Serv...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Problem with xy scatter with dates
I am trying to create an x-y scatter chart where both the x values and the y values are dates. I highlight the 2 adjacent columns of date data and click on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2 lines (the two data columns). The x values on the graph are simply a count of the data points as in a line chart. If the 2 columns of data are not dates, I get the correct scatter chart, but not if the x-values are dates in which case I get a line chart. How can I get a scatter chart with both x-values and y-values as dates? You could select just the Y val...

Expanding Text
Is there any way to have the text overprint more than one line? For example, Excel typed in cell c5 with an 18pt type will cut off the top half of all letters unless the row height is increased. Is it possible to have the text expand into C4 as opposed to increasing the height of the row? Paul Dulong It can extend into d5 if nothing else is there -- Don Guillett SalesAid Software donaldb@281.com "Paul Dulong" <pdulong@cogeco.ca> wrote in message news:imVLd.2555$Sx6.2...

conditional text merge
I want to merge text under the following criteria, First issue (data in Col A in sequence) Col A(text) Col B(text) a gg a hh a s b r b ee c tt d ss d ee Merge to: a gg hh s b r c tt d ss ee Second issue (data in Col A not in sequence) a gg b r a hh a s b ee d ss c tt d ee Merge to: a gg hh s b r c tt d ss ee Is it possible to merge it? TIA norik -- Message posted from http://www.ExcelForum.com Hi yes it is possible but will require some VBA. below one way: 1. Create a unique list for column A on a separate sheet: - selec...

Grey text prints as black..
I'm printing a page that has some text de-emphasized by using a grey font colour. Unfortunately when I print it comes out as black like the rest of the text. I looked for a setting in the Options and in the Print dialogs, but was unable to see anything that said "allow grayscale" anywhere. Suggestions anyone? thanks... R. I have the same problem and have not yet found a solution. could it be a configuration problem with the printer? Have you recently re-installed Excell? Julien "RGK" a écrit : > I'm printing a page that has some text de-empha...

lost graph after convert PDF
I have used Words 2007 to convert to PDF for years and have not problem at all. However, in the last few days, all the graphs in the document are lost after convert into PDF. not knowing why. anyone can help? greatly appreciated. sand Hi, Is your graph connected to a spreadsheet in excel? Make sure that your graph is using values from your Word Document(it may fix the problem). On the other hand, you can try copying and pasting the image onto paint and export it to word as an image(this will be a temporary easy fix but it will do the job if you are on a deadline). Hop...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Dates are not displayed in ComboBox
I have a From ComboBox and a To ComboBox on a Form; cboFrom and cboTo. The Row Source is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; The SQL is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; Nothing is showing in either ComboBox; all I see is a whole bunch of blanks. I just did this in another form in another Access database. I can’t figure out why it’s not working for me now. I’m using Access 2007. I’d sincerely appreciate any insight with this. Thanks! Ryan -- Ryan--...

Year to date sales history of inventory
How can this system provide me with cumulative sales numbers of a particular item ie: How many total gym shorts did we sell in 12 months? Detailed Sales Report with a custom date range. In the body of the report look at the top line (Overall) that will give you the answer you seek my child. Now go caterpillar blossom into a butterfly. Rob "KatieH" wrote: > How can this system provide me with cumulative sales numbers of a particular > item ie: How many total gym shorts did we sell in 12 months? Musta got a new batch o glue, huh? (g) KatieH To pick up where Rob started...

Date Picker
Hello, Is there a DatePicker in Excel 2007 so I can pick a date when I focus on a cell? If not, where can I get one? Thanks, Miguel Look here: http://www.rondebruin.nl/calendar.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "shapper" <mdmoura@gmail.com> wrote in message news:776296c8-83d8-467e-aefb-c31b24446bf7@b15g2000yqd.googlegroups.com... > Hello, > > Is there a DatePicker in Excel 2007 so I can pick a date when I focus > on a cell? > > If not, where can I get one? > > Thanks, > Miguel ...

Deleting Text
I know that with "&" I can add two text cells together. How do I get rid of text? In the example "Roddick, Andy (USA) " in A2, I want to get rid of "(USA) ". I can separate with "=right(A1,7)", the last seven characters, but how to "subtract" them from within A2" Felix In an un-used cell, enter: =LEFT(A2,LEN(A2)-7) then copy this cell and paste/special/value back onto A2 -- Gary''s Student - gsnu200827 "Felix" wrote: > I know that with "&" I can add two text cells together. > > How...

how to find a second comma in a text
Hi, I have a text as --> Madison, Dane, Wisconsin How can I find the position of the second comma? thanks, Boon Assuming there is *always* a 2nd comma... =FIND(",",A2,FIND(",",A2)+1) -- Biff Microsoft Excel MVP "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl... > Hi, > > I have a text as --> Madison, Dane, Wisconsin > > How can I find the position of the second comma? > > thanks, > Boon > > thanks. this works well fo...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

How to write text to command prompt console windows?
I am using VC++ 6.0 to build a windows based application, however, my application allows user to input command arguments in the dos prompt windows. I want to have a warning message for user if they have input the wrong arguments. How can I write text to the dos prompt windows if user input the wrong arguments? Thanks in advance. Steven. Steven wrote: > I am using VC++ 6.0 to build a windows based application, however, my > application allows user to input command arguments in the dos prompt > windows. I want to have a warning message for user if they have input the > wrong ar...

Find and replace text thats not wdAlignParagraphCenter alignment
Im trying to make a macro that set alignment on all text to wdAlignParagraphJustify, except text that is wdAlignParagraphCenter. Right now i have this: With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphRight With .Replacement .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphJustify End With .Execute Replace:=wdReplaceAll End With With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphLeft ...

How can I put text in a circle using Publisher ?
Have looked everywhere for an answer to this ? : How can I put text in a circle while using Publisher ? If I want to surround a shape or piece of clipart with words, I often need to have text in a circular or oval shape ........ surely this can be done with Publisher. Thanks for any expertise. DixieWins wrote: > Have looked everywhere for an answer to this ? : How > can I put text in a circle while using Publisher ? > > If I want to surround a shape or piece of clipart with > words, I often need to have text in a circular or oval > shape ........ surely this can be ...

Need text in upper left corner of a cell
Hi Is it possible to have some text written in upper corner of a cell? I use Excel as a fill-in userform. I can justify the size of other columns, but it would be nice to write something into the cell, so the user understand what to fill inn. Any workarounds or other suggestions? Any help is appreciated. Gunnar Hi maybe you can use the comment functionality (goto 'Insert - Comment') to add some hints for the user -- Regards Frank Kabel Frankfurt, Germany Gunnar wrote: > Hi > > Is it possible to have some text written in upper corner > of a cell? > I use Excel a...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

"Disappearing" lines of text in Word docs
I've noticed a really unusual intermittent bug in my version of MS Word. Suppose there are two consecutive lines of text in my document, Line A and Line B. I want to insert a new line between Lines A & B, so I position the cursor in between them and hit return (not highlighting Line B beforehand, because I'm not trying to get rid of it). When I do so, Line B seems to disappear, with the cursor (and empty line in front of it) taking the place of Line B. However, when I hit "page up" or "page down", Line B reappears. Anyone know why this might ...

How can I cross reference a SmartArt Graphic to a page/text?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to allow users to click on the SmartArt Graphic on my first page and automatically go to the respective page within the document. I click next to the wording on the graphic and go to Insert, but it will not allow me to insert the cross reference. You can do it the other way around - from the text to the graphic, but not from the graphic to the text. Anyone know a way around this or another way to do it? Objects cannot be used as active hyperlinks in a Word document. You can do what you describ...

What date was a batch created
I'm in GP v8.0. I need to know what date a user created and/or saved a batch. I've looked all over and there doesn't seem to be any trail when a batch was created/saved short of turning on the User Tracking system. Please get back to me ASAP - sdanna@sfgmortgage.com First, as you probably know, this is not a forum for getting instant answers because it is not monitored officially. Second, SY00500 stores the batch history. It is in the company database. You will need to create a report based on this table. -- Charles Allen, MVP "Sandy D''Anna, GP Project Ma...