Time from Text

We have a program at work that exports a text field from rotas as
1900-0700 or any variation of. I need to convert this into hours
difference and also run across midnight. I've tried
"=Right(A1,4)-Left(A1,4)", which gets me partially the way there, but
not managed to progress beyond the very basics. I've played with the
TIME function to no avail, i managed to achieve what i wanted over nine
columns but that is ludicrous, any pointers, flashes of inspiration
would be much appreciated. I'm prepared to put this into VBA later, but
would rather have it as a calculation on the sheet


-- 
Maurice


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

0
5/25/2005 8:44:46 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
547 Views

Similar Articles

[PageSpeed] 49

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...

No text in email body
mail delivered to my mail account has no text in the message body. This just started happening in the last few days ?? HELP !!! ...

Color code quoted text
Is there no way to color code quoted text in the standard message reading view? I've searched high and low and can't seem to find anything about this. The newsreader in Opera does a terrific job of this, as do many other programs. Bob Graham Yes, but since you decided to keep secret the version of Outlook you are = using, all bets are off. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, rvgrahamsevatenein@sbcglobal...

when i type i am not seeing any text in Outlook 2003
I am using Microsoft Outlook 2003, I don't know what happened but when I try to send an email nothing appears instead I only see read error lines on the screen. I have tried everything but nothing works. I cannot type because no text appears on my screen. I am not able to send emails. Please help!! After typing a couple of words that you do not see, try to highlight the area where you typed.What happens? Is your font colour set to white? Peter "tigka98@hotmail.com" <tigka98@hotmail.com@discussions.microsoft.com> wrote in message news:777622D7-4C61-432B-B8AD-5...

Text to Columns?
In my spreadsheet, from cells F1 to CA1, I have a list of titles. At the beginning of each of these titles is the words Out of Stock/. I would like to know if there is an easier way to get rid of these words other than text to columns. Right now, I insert a column next to one with text and use Text to Columns and choose "/" as my separator, and then I have to go back and delete all of the unwanted cells. I didn't mind doing this once, but now that I have to do it weekly, I need to find a faster way. TIA This is what it looks like now. F1 ...

How can I capatalize text in a column?
I would like to select a column to incorporate only upper case letters. How do I do this? I would dump the column into Word then do Format - Change Case - UPPERCASE then dump it back into Excel. I do not see this feature in Excel. "dgysr" wrote: > I would like to select a column to incorporate only upper case letters. How > do I do this? will you get your desired results if you use the =UPPER() formula in another column? "dgysr" wrote: > I would like to select a column to incorporate only upper case letters. How > do I do this? ...

Text box disappears on chart
I added a text box to a chart and when I click off of it, the text box disappears (goes behind the chart). I have tried Bring to Front and Bring Forward, and it may or may not stay visible and print. I have clicked on the frame of the text box (it changes pattern) and tried Bring to Front - same problem. Any ideas? Carole O The text box is on the worksheet, not the chart. To fix the problem, Select the text box, and choose Edit>Cut Select the chart (you'll see handles on its corners and sides) Choose Edit>Paste, to paste the text box onto the chart. Click away from the char...

Read Text File into Excel Using VBA
Hi All, I'm a new VBA programmer. I know how to pull an entire text file into an Excel Spreadsheet, but I only want specific information from the text file not the entire text file. What I have is about 25 text files stored in a folder, let's say C:\test. Each file is named by a property address as follows: 209 MAIN ST.txt 213 MAIN ST.txt 111 ELM ST.txt 2356 WOOD AVE.txt On the 11th row of each file is as follows: Property Address:209 MAIN ST On the 31st row of each file is as follows: Total Value:30500 What I would like to do is read each file located in the "C:\test folder...

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 ...

Restructuring text data in excel? Pivot?
Hi! I have a large sum och data in the order below: Choice Course 1 Course 2 Course 3 Course 4 1 Name 1 Name 3 Name 2 Name 1 2 Name 3 Name 4 Name 4 Name 3 3 Name 5 I would like to structure it differently to the format below: Choice 1 2 3 Name 1 Course 1 Course 4 Name 2 Course 3 Name 3 Course 1 Course 2 Course 4 Name 4 Course 2 Course 3 Name 5 Course 1 Is this possible? I have been trying to use pivottables without any luck. Cheers and thanks, Pete Pivottables work with numbers (sum of, max of, average of). They won't work with text. And I don'...

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...

How to customize the length of text preview in desktop alert
How to customize the length of text preview in desktop alert ...

force Excel to treat numbers as text
Sorry I searched this and no one seems to have a solution. I trie pre-defiming columns as text but it gets over-riden by the paste. Past Special doesn't do the trick.. paste special as text on tabular data coming from a web site puts mos of the data in the first column. It no longer spreads the columns o pasted data into its own excel columns the tabular data, tab delimited, I want to 'copy&paste' looks lik this: 1) OGRODOWICZ MARK M47 6306 BROOKLYN NY 533 446 41 24:00 23:25 7:3 20:58 2) CHAO DUSTIN M35 3738 NEW YORK NY 534 447 155 24:02 23:47 7:40 23:1 The paste should...

Using text in a cell in a formula
I need to have a formula use the text of adjacent cells to determine where to look for values. I want to grab data from multiple datasheets but I don't want to have to type the sheet name in every formula, I want the formula to grab the name of the sheet from an adjacent cell. i.e. I need a formula like =if(sheet1!B2>0,100,""), but I need a function that will fill in the sheet name from the text value of an adjacent cell that would have the sheet name in it as I will be doing this for multiple cells on the same sheet. see your other post -- Biff Microsoft...

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: ...

Removing text from cells
Hi, I have a spreadsheet with a list of reference numbers in column A. The numbers are like this. AAA/12345/Smith J Mr What I want to do is remove the text after the last / eg. Smith J Mr. I have thousands of cells I need to remove this on. Please help. Thanks. Hi Craig are all the entries the same number of characters for the first two sections, AAA/12345/ BBB/67890/ etc if so you can do this using a "helper" column - in column B type ==LEFT(A1,10) and then double click on the fill handle (bottom right hand side of cell) to fill down now copy column B click on A1 and ch...

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...

setup a text box as a lined page
I'd like to simulate text on lined paper. Is it possible in Word or Publisher? Thanks Find a lined paper background or graphic, put it on the Master page. On the the publication page create a text box. View, turn on baseline guides. Go to Arrange, Layout guides, baseline tab, adjust it to match your lined graphic. Select the text box, Format, paragraph, check align text to baseline guides. http://images.google.com/images?hl=en&q=lined+paper&gbv=2 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "tridev" <tr...

I'm having problems inserting text files from notepad
When I try to insert a text file created in notepad, which contains characters such as £, $ or letters with symbols over them, into a text box in Publisher 2003 it comes up with a File Conversion window, and the options to choose the Text encoding option. This didnt happen in our older version of Publisher, on our old PCs. We have new PC running Windows XP and Office Professional 2003 and I want to be able to insert the files without this window appearing. I have checked the registry and CharacterSet value is set to ANSI. Any suggestions on what is happening and how I can correct...

Sort Column by Text Color or BackGrd Color
Does anyone have a method that allows you to sort by Text Color or by Background Color? Darn! It's so cold here in Massachussetts that my fingers hit the wrong key. Check out Chip Pearson's page. http://www.cpearson.com/excel/SortByColor.htm Good Luck, Mark Graesser mark_graesser@yahoo.com ----- Ken Dickens wrote: ----- Does anyone have a method that allows you to sort by Text Color or by Background Color? Ken, The principle is to create a UDF to calculate the Colorindex and add it to an adjacent column, and then sort by that column. Chip Pea...

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) ....

Need to change text to csv
My credit card company has an export feature. One of the options is ".csv (Excel). This used to give me a nice clean spreadsheet with each column of information in a separate Excell column. I guess it converted on the fly or something. But now it seems to be a "comma delimited text file" In other words, only the first Excel cell in each row has data in it, and it looks like this in each row (the first row has headings). 1,"03/24/2005","HARVARD CS","Credit","$39.95". What can I do to get all those comma delimited items into sep...

Can't get a value from a querry into a forms text box
I have a main Shippin Control table, and a main Shipping Control form with description of cargo and quantity text boxes. I have a querry which is a join of tblValues and tblShipping_Control. The SQL is below SELECT Shipping_Control.DescriptionofCargo, Shipping_Control.CargoQty, tblValues.Value, ([Shipping_Control].[CargoQty]*[tblValues].[Value]) AS CommercialValue, Shipping_Control.ShippingDate, Shipping_Control.ShipTo, Shipping_Control.ShipFrom, Shipping_Control.CollectionNoteNo, Shipping_Control.PackingListNo, Shipping_Control.ShipmentID, Shipping_Control.Customer FROM Ship...

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...

Text Rotation in Word 2007
I am trying to rotate a small text box in Word 2007. In Word Help, it says under "Rotate a picture, shape, text box, or WordArt " :- "Click the picture shape, text box or WordArt that you want to rotate. Do one of the following: Drag the rotation handle" But I can not find the rotation handle - I am going mad trying to get it to work. Sorry for being such an idiot, but can someone tell me what I am doing wrong? In addition, under the Text Box Format Menu, there is a 'Rotate' button which when you click it has all the options greyed out except &#...