Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
Hi all,
Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
In Excel, if only the dates are netred then the dates are actually stored as
numbers (integers) and when you add time to date then that numbers become decimal numbers. Actually, if you change the format of the cell to numbers without decimals you will get the result. -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "goss" wrote: Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
Assuming that the data is in Text format, in a work area, say E1 tru F12
enter this table: JAN 1 FEB 2 MAR 3 APR 4 MAY 5 JUN 6 JUL 7 AUG 8 SEP 9 OCT 10 NOV 11 DEC 12 Then with your value in A1: =DATE(2000+MID(A1,8,2),VLOOKUP(LEFT(A1,3),E1:F12,2 ,FALSE),MID(A1,5,2)) -- Gary''s Student - gsnu200812 "goss" wrote: Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
You can use Text to Columns.
Specify a fixed width for two columns, one for the date, and one for the time. You can discard the time, if you want, by skipping the column. Regards, Fred. "goss" wrote in message ... Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote:
Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss If all of your dates will be in this century, then: and if the format will always be: mmm dd\'yy h:mmAM/PM =--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20") If the format of the string may be different, then post back with more details. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Nov 7, 5:43*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote: Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss If all of your dates will be in this century, then: and if the format will always be: mmm dd\'yy h:mmAM/PM =--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20") If the format of the string may be different, then post back with more details. --ron Thanks all! Ron, Works great! Yes, The String is extracted from a UNIX report and will always be same format But instead of adding a helper column can I use For Next Loop to go through the range and use approximately the same function? I tried but received "Compile error: Argument not optional" Here is my code snippet 'Replace For Each C In myRange C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "), 7, 1, ", 20") Next C Thanks! goss |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Fri, 7 Nov 2008 06:34:19 -0800 (PST), goss wrote:
On Nov 7, 5:43*am, Ron Rosenfeld wrote: On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote: Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss If all of your dates will be in this century, then: and if the format will always be: mmm dd\'yy h:mmAM/PM =--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20") If the format of the string may be different, then post back with more details. --ron Thanks all! Ron, Works great! Yes, The String is extracted from a UNIX report and will always be same format But instead of adding a helper column can I use For Next Loop to go through the range and use approximately the same function? I tried but received "Compile error: Argument not optional" Here is my code snippet 'Replace For Each C In myRange C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "), 7, 1, ", 20") Next C Thanks! goss Yes you can, but VBA is a bit "smarter", so you could use something like: For Each c In MyRange s = Replace(c.Value, "'", ", 20") c.Value = DateValue(s) + TimeValue(s) c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Nov 7, 11:52*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 06:34:19 -0800 (PST), goss wrote: On Nov 7, 5:43*am, Ron Rosenfeld wrote: On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote: Hi all, Is there a way to convert this date string OCT 23'08 6:04PM into serial that Excel will recognize as a number instead of a string? Thanks! goss If all of your dates will be in this century, then: and if the format will always be: mmm dd\'yy h:mmAM/PM =--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20") If the format of the string may be different, then post back with more details. --ron Thanks all! Ron, Works great! Yes, The String is extracted from a UNIX report and will always be same format But instead of adding a helper column can I use For Next Loop to go through the range and use approximately the same function? I tried but received "Compile error: Argument not optional" Here is my code snippet * *'Replace * *For Each C In myRange * * * *C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "), 7, 1, ", 20") * *Next C Thanks! goss Yes you can, but VBA is a bit "smarter", so you could use something like: For Each c In MyRange * * s = Replace(c.Value, "'", ", 20") * * c.Value = DateValue(s) + TimeValue(s) * * c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c --ron- Hide quoted text - - Show quoted text - Thanks Ron, Option Explicit is on I was not sure of data type for s? I tried S as string and long Neither worked I receive A Typ Mismatch Error Message Thanks! goss |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote:
Thanks Ron, Option Explicit is on I was not sure of data type for s? I tried S as string and long Neither worked I receive A Typ Mismatch Error Message Thanks! goss s is String. As in: ====================== Option Explicit Sub foo() Dim s As String Dim MyRange As Range, c As Range Set MyRange = Range("a1") 'obviously should be changed. For Each c In MyRange s = Replace(c.Value, "'", ", 20") c.Value = DateValue(s) + TimeValue(s) c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c End Sub ======================= --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Nov 7, 8:17*pm, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote: Thanks Ron, Option Explicit is on I was not sure of data type for s? I tried S as string and long Neither worked I receive A Typ Mismatch Error Message Thanks! goss s is String. As in: ====================== Option Explicit Sub foo() Dim s As String Dim MyRange As Range, c As Range Set MyRange = Range("a1") 'obviously should be changed. For Each c In MyRange * * s = Replace(c.Value, "'", ", 20") * * c.Value = DateValue(s) + TimeValue(s) * * c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c End Sub ======================= --ron Thanks Ron, My extract code had a handler so if the date was not found a zero was returned Once the date is found, fill down to all records So rows 1 and 2 had zeroes, all other rows had the date string I added a test for If Not IsNumeric(C.Value) and now the code works great! Thanks! goss Snippet: 'Replace For Each C In myRange If Not IsNumeric(C.Value) Then s = Replace(C.Value, "'", ", 20") C.Value = DateValue(s) + TimeValue(s) C.NumberFormat = "MM/DD/YYYY" End If Next C |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Sat, 8 Nov 2008 06:13:43 -0800 (PST), goss wrote:
On Nov 7, 8:17*pm, Ron Rosenfeld wrote: On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote: Thanks Ron, Option Explicit is on I was not sure of data type for s? I tried S as string and long Neither worked I receive A Typ Mismatch Error Message Thanks! goss s is String. As in: ====================== Option Explicit Sub foo() Dim s As String Dim MyRange As Range, c As Range Set MyRange = Range("a1") 'obviously should be changed. For Each c In MyRange * * s = Replace(c.Value, "'", ", 20") * * c.Value = DateValue(s) + TimeValue(s) * * c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c End Sub ======================= --ron Thanks Ron, My extract code had a handler so if the date was not found a zero was returned Once the date is found, fill down to all records So rows 1 and 2 had zeroes, all other rows had the date string I added a test for If Not IsNumeric(C.Value) and now the code works great! Thanks! goss Snippet: 'Replace For Each C In myRange If Not IsNumeric(C.Value) Then s = Replace(C.Value, "'", ", 20") C.Value = DateValue(s) + TimeValue(s) C.NumberFormat = "MM/DD/YYYY" End If Next C Glad to see you got it working. Although if you have a zero in a cell, IsNumeric should test TRUE. Another approach would be to add an error handler, with an appropriate message depending on the error. --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date String To Serial
On Nov 8, 7:45*am, Ron Rosenfeld wrote:
On Sat, 8 Nov 2008 06:13:43 -0800 (PST), goss wrote: On Nov 7, 8:17*pm, Ron Rosenfeld wrote: On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote: Thanks Ron, Option Explicit is on I was not sure of data type for s? I tried S as string and long Neither worked I receive A Typ Mismatch Error Message Thanks! goss s is String. As in: ====================== Option Explicit Sub foo() Dim s As String Dim MyRange As Range, c As Range Set MyRange = Range("a1") 'obviously should be changed. For Each c In MyRange * * s = Replace(c.Value, "'", ", 20") * * c.Value = DateValue(s) + TimeValue(s) * * c.NumberFormat = "mmm dd, yyyy h:mm am/pm" Next c End Sub ======================= --ron Thanks Ron, My extract code had a handler so if the date was not found a zero was returned Once the date is found, fill down to all records So rows 1 and 2 had zeroes, all other rows had the date string I added a test for If Not IsNumeric(C.Value) and now the code works great! Thanks! goss Snippet: * *'Replace * * * *For Each C In myRange * * * * * *If Not IsNumeric(C.Value) Then * * * * * * * *s = Replace(C.Value, "'", ", 20") * * * * * * * *C.Value = DateValue(s) + TimeValue(s) * * * * * * * *C.NumberFormat = "MM/DD/YYYY" * * * * * *End If * * * *Next C Glad to see you got it working. Although if you have a zero in a cell, IsNumeric should test TRUE. Another approach would be to add an error handler, with an appropriate message depending on the error. --ron- Hide quoted text - - Show quoted text - Thanks Ron, The test does return TRUE for the first 2 rows, so the zeroes are not formatted and no type mismatch error is received The code then executes as expected for all remaining rows Thanks! goss |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
serial value to date in excel | Excel Worksheet Functions | |||
Serial Numbers converted to text string | Excel Worksheet Functions | |||
Serial number of Date | Excel Worksheet Functions | |||
date displays as serial number | Excel Discussion (Misc queries) | |||
How do I convert Date serial number to date | Excel Worksheet Functions |