date time formatting

bagpeople · Apr 16th, 2002, 05:06 PM

Can anyone help me with formatting a peculier numeric string? I have data output from a serial device that includes the following date/time string in "yy/mm/dd/hh:mm:ss" ie: "01/04/11/12:22:51"
I can get excel to format all kinds of date/time combos via custom number formats. Doesn't seem to like this one.
excel 2k sr-1

Tom Schreiner · Apr 16th, 2002, 05:11 PM

Hi
Assuming your data is in Column A
Run this down Column B
Format to suit

=LEFT(A1,8) & " " & RIGHT(A1,8)

Tom

Jack in the UK · Apr 16th, 2002, 05:18 PM

Hi dont see a problem there i would edit thou:

try

yy/mm/dd hh:mm:ss

should work OK! custom format the cell

bagpeople · Apr 16th, 2002, 05:48 PM

Thanks Tom! That takes care of my first stumbling block, seperating the date and time. Wouldn't be a big deal except I will be looking at thousands of them!
Is there a way I can either rearrange the date to a western standard or convince excel to show it as 2001-Aug-2?

Allan

[ This Message was edited by: bagpeople on 2002-04-16 16:51 ]

Apr 16th, 2002, 05:18 PM	#3
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi dont see a problem there i would edit thou: try yy/mm/dd hh:mm:ss should work OK! custom format the cell __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 05:06 PM	#1
bagpeople New Member Join Date: Apr 2002 Posts: 2	Can anyone help me with formatting a peculier numeric string? I have data output from a serial device that includes the following date/time string in "yy/mm/dd/hh:mm:ss" ie: "01/04/11/12:22:51" I can get excel to format all kinds of date/time combos via custom number formats. Doesn't seem to like this one. excel 2k sr-1

Apr 16th, 2002, 05:11 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Assuming your data is in Column A Run this down Column B Format to suit =LEFT(A1,8) & " " & RIGHT(A1,8) Tom

Apr 16th, 2002, 05:48 PM	#4
bagpeople New Member Join Date: Apr 2002 Posts: 2	Thanks Tom! That takes care of my first stumbling block, seperating the date and time. Wouldn't be a big deal except I will be looking at thousands of them! Is there a way I can either rearrange the date to a western standard or convince excel to show it as 2001-Aug-2? Allan [ This Message was edited by: bagpeople on 2002-04-16 16:51 ]