need help splitting a name form a date
basically I have a column of name+date i.e. "John Smith 05/01/06". What I need to do in a dummy column is put a formula that returns only the date. The problem I have is that some of the dates are in format 05/01/06 and some are 05/01/2006 so the number of characters for the date is not consistent (so i cannot just use the Right runction). anyone know a way of doing this? thanks -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940 View this thread: http://www.excelforum.com/showthread...hreadid=505307 |
need help splitting a name form a date
=MID(A11,FIND("~",SUBSTITUTE(A11," ","~",LEN(A11)-LEN(SUBSTITUTE(A11,"
",""))))+1,99) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "neowok" wrote in message ... basically I have a column of name+date i.e. "John Smith 05/01/06". What I need to do in a dummy column is put a formula that returns only the date. The problem I have is that some of the dates are in format 05/01/06 and some are 05/01/2006 so the number of characters for the date is not consistent (so i cannot just use the Right runction). anyone know a way of doing this? thanks -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940 View this thread: http://www.excelforum.com/showthread...hreadid=505307 |
need help splitting a name form a date
=RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND(" ",A1))
will give you the date as text =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND(" ",A1))) will give you the date as an excel date (format as date to show a date) You could also use the Data-Text to Columns function. Select delimited and use a space as the delimiter. "neowok" wrote: basically I have a column of name+date i.e. "John Smith 05/01/06". What I need to do in a dummy column is put a formula that returns only the date. The problem I have is that some of the dates are in format 05/01/06 and some are 05/01/2006 so the number of characters for the date is not consistent (so i cannot just use the Right runction). anyone know a way of doing this? thanks -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940 View this thread: http://www.excelforum.com/showthread...hreadid=505307 |
need help splitting a name form a date
thanks thats worked nicely. -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940 View this thread: http://www.excelforum.com/showthread...hreadid=505307 |
All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com