|
date & month
In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of
ddmmyy. how can i get date and month r in cell A2 & A3 from this. e.g. A1 A2 A3 NW/RON/ENT/05/010209 01 FEB 09 FEB NW/SR-B/ENT/04/030509 03 MAY 09 MAY |
date & month
Hi
I am assuming that you really mean you want the Date in B1 and the Month in C1. In B1 enter =RIGHT(A1,6) Copy down as far as required Copy column BPaste SpecialValues Select column BDataText to ColumnsNextNextDateD/M/YFinish In cell C1 =TEXT(B1,"mmm") Copy down as required -- Regards Roger Govier "aditya" wrote in message ... In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of ddmmyy. how can i get date and month r in cell A2 & A3 from this. e.g. A1 A2 A3 NW/RON/ENT/05/010209 01 FEB 09 FEB NW/SR-B/ENT/04/030509 03 MAY 09 MAY |
date & month
On Tue, 2 Jun 2009 23:54:01 -0700, aditya
wrote: In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of ddmmyy. how can i get date and month r in cell A2 & A3 from this. e.g. A1 A2 A3 NW/RON/ENT/05/010209 01 FEB 09 FEB NW/SR-B/ENT/04/030509 03 MAY 09 MAY To extract the last six characters and change it into a date, you can use this formula: =DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)) You can use that formula in A2 and A3 and custom format according to your requirements, or you can wrap a TEXT function around the formula to give a similar display (although one that you may not be able to use in any calculations. So, using the TEXT function method: A2: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy") A3: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm") --ron |
date & month
Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to =--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either case) be =TEXT(A2,"mmm"). You may need to use UPPER() if you insist on upper case. -- David Biddulph "Ron Rosenfeld" wrote in message ... On Tue, 2 Jun 2009 23:54:01 -0700, aditya wrote: In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of ddmmyy. how can i get date and month r in cell A2 & A3 from this. e.g. A1 A2 A3 NW/RON/ENT/05/010209 01 FEB 09 FEB NW/SR-B/ENT/04/030509 03 MAY 09 MAY To extract the last six characters and change it into a date, you can use this formula: =DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)) You can use that formula in A2 and A3 and custom format according to your requirements, or you can wrap a TEXT function around the formula to give a similar display (although one that you may not be able to use in any calculations. So, using the TEXT function method: A2: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy") A3: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm") --ron |
date & month
On Wed, 3 Jun 2009 11:09:41 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote: Or if you have your Windows Regional Options set to recognisedates as ddmmyy, you can simplify Ron's A2 formula to =--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either case) be =TEXT(A2,"mmm"). You may need to use UPPER() if you insist on upper case. -- David Biddulph Of course, that solution is critically dependent on the Windows Regional settings. It won't work, for example, with standard US settings. The solution I offered should work no matter how the Windows Regional settings are set. --ron |
date & month
On Wed, 03 Jun 2009 05:38:49 -0400, Ron Rosenfeld
wrote: On Tue, 2 Jun 2009 23:54:01 -0700, aditya wrote: In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of ddmmyy. how can i get date and month r in cell A2 & A3 from this. e.g. A1 A2 A3 NW/RON/ENT/05/010209 01 FEB 09 FEB NW/SR-B/ENT/04/030509 03 MAY 09 MAY To extract the last six characters and change it into a date, you can use this formula: =DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)) You can use that formula in A2 and A3 and custom format according to your requirements, or you can wrap a TEXT function around the formula to give a similar display (although one that you may not be able to use in any calculations. So, using the TEXT function method: A2: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy") A3: =TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2) +100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30), MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM( RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm") --ron Stupid me. Much simpler: For the date: =DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30), LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)) and for the formatted date: =TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30), LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"dd mmm yy") and for the formatted month: =TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30), LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"mmm") --ron |
date & month
Agreed. Hence the first clause of my reply.
-- David Biddulph "Ron Rosenfeld" wrote in message ... On Wed, 3 Jun 2009 11:09:41 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: Or if you have your Windows Regional Options set to recognisedates as ddmmyy, you can simplify Ron's A2 formula to =--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either case) be =TEXT(A2,"mmm"). You may need to use UPPER() if you insist on upper case. -- David Biddulph Of course, that solution is critically dependent on the Windows Regional settings. It won't work, for example, with standard US settings. The solution I offered should work no matter how the Windows Regional settings are set. --ron |
| All times are GMT +1. The time now is 03:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com