Excel Question

...then enter into your target cell: "=SourceCell" (for instance, if your original value is in cell A 3, then enter: "=A3" into, say, B 3, or wherever you want the result to go).

And then format the target cell (B 3, or whatever, as per the above) to show only the time. The easiest (? IMO) way to do that is this: Right-click it, select "Format Cells..." from the pop-up menu (it's fourth from the bottom). In the "Format Cells" dialog[ue] that appears, select the (left-most) "Number" tab, and in the "Category" list box on the left, select "Time" (about half-way down on my system). In the "Type" list box on the right, there should be a value like "1:30:55 PM" (at the bottom, on my system); select that, click "OK", and you should be done.

(If you can't find that alternative, you can either futz around with the "Locale (location)" drop-down selector, or switch to the "Custom" category in the list box on the left. The correct Time-category selection seems to be equivalent to putting the value "[$-409]h:mm:ss AM/PM;@" into the "Type" edit line above the list box on the right. I'm not sure what the "[$-409]" and ";@" bits are all about; it seems to me that a simple "h:mm:ss AM/PM" does also work [but that could of course be just on my system].)

HTH!

Post #195,990 by alvitale 2/22/05 4:24:57 PM Reply	Excel Question Can someone help me with an Excel programming problem? I would like move the 12 right most characters from one cell to another. Cell A contains "1/14/2003 1:02:14 PM" I would like to move " 1:02:14 PM" to cell B. Does it make any difference if the .xls contains 6k records? Thanks for your help. Al Vitale
Post #195,993 by pwhysall 2/22/05 4:27:40 PM Reply	If that's an actual time... (rather than literal text) you can simply copy it to cell B and format it as you wish. Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #195,995 by CRConrad 2/22/05 4:28:56 PM Reply	Enter in TargetCell: "=Right(SourceCell, 12)" HTH! (HTW :-)
Post #195,996 by pwhysall 2/22/05 4:29:29 PM Reply	Will only work for string literals Hence my suggestion to reformat :) Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #195,997 by CRConrad 2/22/05 4:30:56 PM 2/22/05 4:31:13 PM Reply	Al's question looks like string literals are what it's about Edited by CRConrad Feb. 22, 2005, 04:31:13 PM EST Expand All History
Post #195,998 by pwhysall 2/22/05 4:31:42 PM Reply	Looks like Excel's default rendition of a date/time serial Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #196,044 by CRConrad 2/23/05 2:18:14 AM Reply	Default? On Yank Winders, perhaps, yes... ...but not on mine (and not on yours, I'd have thought), where the default Windows date and time formats -- which Excel obeys, AFAIK -- are different. I mean, as long as you want to be quite exact about these things... (And people call me "anal"?!?) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #196,048 by pwhysall 2/23/05 3:25:53 AM Reply	Al's Winders will be Yankish, I wot. Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #196,051 by CRConrad 2/23/05 3:50:57 AM Reply	Quite possib -- hey, quite probable, even! ;-)
Post #196,065 by ChrisR 2/23/05 9:44:53 AM Reply	And they'll all buffer overflow on 12/31/2024
Post #196,066 by CRConrad 2/23/05 9:46:57 AM Reply	Why then; izzat when Al's Social Security runs out, or...?
Post #196,010 by bepatient 2/22/05 6:29:15 PM Reply	Use text->column Tell it that the source is delimited by a space. You will get x/x/xxx then 1:11:11 then AM. You need to have the 2 columns to the right open to accept the data from the split. Excel isn't smart enough to add the columns for this operation. If you push something hard enough, it will fall over. _{Fudd's First Law of Opposition} [link\|mailto:bepatient@aol.com\|BePatient]
Post #196,038 by pwhysall 2/23/05 1:40:12 AM Reply	People! This string stuff won't work! Dollars to donuts column A is a date/time value - i.e. a number. Excel is formatting it mm/dd/yyyy HH:MM:SS PM This is not a string you can do "left() right(), mid() etc" on. It's a NUMBER. Of course, if it really is a string, then ignore me. But I bet it isn't. Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #196,045 by CRConrad 2/23/05 2:24:54 AM Reply	Yep, yer probably right. Peculiar, though. I would have... ...thought that Excel would forcibly convert it to a string all by itself, and then taken the right-most sub-string of that -- it certainly does enough "helpful" stuff like that when you don't want it to, so why on Earth not now? Weirdski. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #196,046 by CRConrad 2/23/05 2:50:54 AM Reply	If Peter is right, and that's a numeric date-time value... ...then enter into your target cell: "`=SourceCell`" (for instance, if your original value is in cell A 3, then enter: "`=A3`" into, say, B 3, or wherever you want the result to go). And then format the target cell (B 3, or whatever, as per the above) to show only the time. The easiest (? IMO) way to do that is this: Right-click it, select "`Format Cells...`" from the pop-up menu (it's fourth from the bottom). In the "Format Cells" dialog[ue] that appears, select the (left-most) "Number" tab, and in the "Category" list box on the left, select "Time" (about half-way down on my system). In the "Type" list box on the right, there should be a value like "`1:30:55 PM`" (at the bottom, on my system); select that, click "OK", and you should be done. (If you can't find that alternative, you can either futz around with the "Locale (location)" drop-down selector, or switch to the "Custom" category in the list box on the left. The correct Time-category selection seems to be equivalent to putting the value "`[$-409]h:mm:ss AM/PM;@`" into the "Type" edit line above the list box on the right. I'm not sure what the "`[$-409]`" and "`;@`" bits are all about; it seems to me that a simple "`h:mm:ss AM/PM`" does also work [but that could of course be just on my system].) HTH! [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #196,053 by bepatient 2/23/05 7:10:49 AM Reply	Correct if its simply a format issue...copy the cell directly and change the format. If you push something hard enough, it will fall over. _{Fudd's First Law of Opposition} [link\|mailto:bepatient@aol.com\|BePatient]
Post #196,054 by pwhysall 2/23/05 7:12:16 AM Reply	/me falls over You owe me a beer! :-p Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #196,112 by bepatient 2/23/05 2:37:50 PM Reply	Settle down fella You'll get plenty of them in a couple months. If you push something hard enough, it will fall over. _{Fudd's First Law of Opposition} [link\|mailto:bepatient@aol.com\|BePatient]
Post #196,064 by CRConrad 2/23/05 9:44:12 AM Reply	Well, yes- but maybe he wants the original one also. (Al?) Also, "=[Left-Arrow]" (or whatever) gives a neat and easy value to copy down into a lot of cells, if he actually has a whole column of values he wants to change(). Oh well, a matter of taste, I suppose; YMMV. (): Get a correct value/format into the topmost one, Al, and then "paint" (i.e, multi-select) the rest of them by click-dragging with the mouse, or pressing shift-[Down-Arrow]; then finally press Ctrl-D. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #196,126 by Steven A S 2/23/05 3:33:19 PM Reply	If you want a text result... from date source you can use something similar to: =TEXT(A1,"h:mm:ss AM/PM") This seems to work from a text source as well as long as it's in the format you show. but the easiest way as has been said, is to format the cells to show just the time ~~~)-Steven---- "I want you to remember that no bastard ever won a war by dying for his country. He won it by making the other poor dumb bastard die for his country..." General George S. Patton
Post #196,358 by alvitale 2/25/05 4:27:25 PM Reply	Thanks to all who contributed. I didn't respond to the because the person who sent the data has not yet replied to me. Thanks again everyone. Al Vitale

Welcome to IWETHEY!