Date Function
in [Functions]
|
Prev: how do i find out what data is hidden i excel
Next: Adding and then sorting data into an already existing Excel 2007 w
From: Rick Rothstein on 27 Mar 2010 13:50 Here is a slightly different "atomic option" from the one you posted which does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g>)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:ubzGADVzKHA.1236(a)TK2MSFTNGP06.phx.gbl... > Well, this formula has a potential flaw *if* the date is followed by > another character like a punctuation mark. > > Maybe the "atomic option" is best afterall. > > -- > Biff > Microsoft Excel MVP > > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl... >> Thanks! >> >> But, I think I'm using an atomic bomb to kill an ant! >> >> That formula is a generic formula to extract a number from a string. >> >> I like Luke's suggestion but it needs tweaked a bit. >> >> Let's assume that there are no other numbers in the string and the date >> is *always* in the format m/d/yyyy or m/dd/yyyy. >> >> Luke's formula could fail when the date is at the start of the string. >> >> Consider these strings: >> >> 1/1/2010 is the deadline >> 1/10/2010 is the deadline >> 10/1/2010 is the deadline >> 10/10/2010 is the deadline >> >> The deadline is 1/1/2010 >> The deadline is 1/10/2011 >> The deadline is 10/1/2010 >> The deadline is 10/10/2010 >> >> The deadline of 1/1/2010 is firm >> The deadline of 1/10/2010 is firm >> The deadline of 10/1/2010 is firm >> The deadline of 10/10/2010 is firm >> >> So, Luke's formula with a tweak will account for all of the above: >> >> =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Ron(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message >> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com... >>> Brilliant Biff >>> >>> "T. Valko" wrote: >>> >>>> Try this... >>>> >>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))) >>>> >>>> Format as Date >>>> >>>> -- >>>> Biff >>>> Microsoft Excel MVP >>>> >>>> >>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com... >>>> > If a cell has text and numeric (date) is there a way to pull the date >>>> > out >>>> > easily ? >>>> > E.g. >>>> > Product dropped off on 11/01/2009 by Michelle Smith >>>> > I would like to see 11/01/2009 >>>> > >>>> > Thank you >>>> >>>> >>>> . >>>> >> >> > >
From: T. Valko on 27 Mar 2010 17:07 >does not contain the Volatile INDIRECT function >ROW($1:$99) Well, you can either use the volatile INDIRECT and be robust against row insertions or you can use ROW and hope you don't ever need to insert rows from row 1 to 99. Of course, if the date is *always* in a specific format where the length of the date string is *always* the same then it could be as simple as: =--MID(A1,FIND("/",A1)-n1,n2) Where n1 = 1 or 2, the length of the month portion of the date and n2 = the total length of the date string. This would be very easy if there was a SUBSTITUTE / REPLACE type function that would take arrays as the old_text argument! -- Biff Microsoft Excel MVP "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:OlSa5XdzKHA.4328(a)TK2MSFTNGP04.phx.gbl... > Here is a slightly different "atomic option" from the one you posted which > does not contain the Volatile INDIRECT function call (plus it's 2 > characters shorter<g>)... > > =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99))) > > -- > Rick (MVP - Excel) > > > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:ubzGADVzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >> Well, this formula has a potential flaw *if* the date is followed by >> another character like a punctuation mark. >> >> Maybe the "atomic option" is best afterall. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >> news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl... >>> Thanks! >>> >>> But, I think I'm using an atomic bomb to kill an ant! >>> >>> That formula is a generic formula to extract a number from a string. >>> >>> I like Luke's suggestion but it needs tweaked a bit. >>> >>> Let's assume that there are no other numbers in the string and the date >>> is *always* in the format m/d/yyyy or m/dd/yyyy. >>> >>> Luke's formula could fail when the date is at the start of the string. >>> >>> Consider these strings: >>> >>> 1/1/2010 is the deadline >>> 1/10/2010 is the deadline >>> 10/1/2010 is the deadline >>> 10/10/2010 is the deadline >>> >>> The deadline is 1/1/2010 >>> The deadline is 1/10/2011 >>> The deadline is 10/1/2010 >>> The deadline is 10/10/2010 >>> >>> The deadline of 1/1/2010 is firm >>> The deadline of 1/10/2010 is firm >>> The deadline of 10/1/2010 is firm >>> The deadline of 10/10/2010 is firm >>> >>> So, Luke's formula with a tweak will account for all of the above: >>> >>> =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) >>> >>> -- >>> Biff >>> Microsoft Excel MVP >>> >>> >>> "Ron(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message >>> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com... >>>> Brilliant Biff >>>> >>>> "T. Valko" wrote: >>>> >>>>> Try this... >>>>> >>>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))) >>>>> >>>>> Format as Date >>>>> >>>>> -- >>>>> Biff >>>>> Microsoft Excel MVP >>>>> >>>>> >>>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >>>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com... >>>>> > If a cell has text and numeric (date) is there a way to pull the >>>>> > date out >>>>> > easily ? >>>>> > E.g. >>>>> > Product dropped off on 11/01/2009 by Michelle Smith >>>>> > I would like to see 11/01/2009 >>>>> > >>>>> > Thank you >>>>> >>>>> >>>>> . >>>>> >>> >>> >> >>
From: Rick Rothstein on 27 Mar 2010 19:57 Insertions do not seem to affect either of our formulas as far as I can see. -- Rick (MVP - Excel) "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:uzpk4FfzKHA.6140(a)TK2MSFTNGP05.phx.gbl... >>does not contain the Volatile INDIRECT function >>ROW($1:$99) > > Well, you can either use the volatile INDIRECT and be robust against row > insertions or you can use ROW and hope you don't ever need to insert rows > from row 1 to 99. > > Of course, if the date is *always* in a specific format where the length > of the date string is *always* the same then it could be as simple as: > > =--MID(A1,FIND("/",A1)-n1,n2) > > Where n1 = 1 or 2, the length of the month portion of the date and n2 = > the total length of the date string. > > This would be very easy if there was a SUBSTITUTE / REPLACE type function > that would take arrays as the old_text argument! > > -- > Biff > Microsoft Excel MVP > > > "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message > news:OlSa5XdzKHA.4328(a)TK2MSFTNGP04.phx.gbl... >> Here is a slightly different "atomic option" from the one you posted >> which does not contain the Volatile INDIRECT function call (plus it's 2 >> characters shorter<g>)... >> >> =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99))) >> >> -- >> Rick (MVP - Excel) >> >> >> >> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >> news:ubzGADVzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >>> Well, this formula has a potential flaw *if* the date is followed by >>> another character like a punctuation mark. >>> >>> Maybe the "atomic option" is best afterall. >>> >>> -- >>> Biff >>> Microsoft Excel MVP >>> >>> >>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >>> news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl... >>>> Thanks! >>>> >>>> But, I think I'm using an atomic bomb to kill an ant! >>>> >>>> That formula is a generic formula to extract a number from a string. >>>> >>>> I like Luke's suggestion but it needs tweaked a bit. >>>> >>>> Let's assume that there are no other numbers in the string and the date >>>> is *always* in the format m/d/yyyy or m/dd/yyyy. >>>> >>>> Luke's formula could fail when the date is at the start of the string. >>>> >>>> Consider these strings: >>>> >>>> 1/1/2010 is the deadline >>>> 1/10/2010 is the deadline >>>> 10/1/2010 is the deadline >>>> 10/10/2010 is the deadline >>>> >>>> The deadline is 1/1/2010 >>>> The deadline is 1/10/2011 >>>> The deadline is 10/1/2010 >>>> The deadline is 10/10/2010 >>>> >>>> The deadline of 1/1/2010 is firm >>>> The deadline of 1/10/2010 is firm >>>> The deadline of 10/1/2010 is firm >>>> The deadline of 10/10/2010 is firm >>>> >>>> So, Luke's formula with a tweak will account for all of the above: >>>> >>>> =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) >>>> >>>> -- >>>> Biff >>>> Microsoft Excel MVP >>>> >>>> >>>> "Ron(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message >>>> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com... >>>>> Brilliant Biff >>>>> >>>>> "T. Valko" wrote: >>>>> >>>>>> Try this... >>>>>> >>>>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))) >>>>>> >>>>>> Format as Date >>>>>> >>>>>> -- >>>>>> Biff >>>>>> Microsoft Excel MVP >>>>>> >>>>>> >>>>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >>>>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com... >>>>>> > If a cell has text and numeric (date) is there a way to pull the >>>>>> > date out >>>>>> > easily ? >>>>>> > E.g. >>>>>> > Product dropped off on 11/01/2009 by Michelle Smith >>>>>> > I would like to see 11/01/2009 >>>>>> > >>>>>> > Thank you >>>>>> >>>>>> >>>>>> . >>>>>> >>>> >>>> >>> >>> > >
From: T. Valko on 27 Mar 2010 21:31 A1 = 1/1/2009 is the start date. It might be far-fetched but if you inserted 9 or more new rows at the top of the sheet then ROW($1:$99) becomes ROW($10:$108) etc. Then LEFT starts with 10 characters and the date portion by itself is never evaluated. -- Biff Microsoft Excel MVP "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:Ow02%23kgzKHA.2436(a)TK2MSFTNGP04.phx.gbl... > Insertions do not seem to affect either of our formulas as far as I can > see. > > -- > Rick (MVP - Excel) > > > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:uzpk4FfzKHA.6140(a)TK2MSFTNGP05.phx.gbl... >>>does not contain the Volatile INDIRECT function >>>ROW($1:$99) >> >> Well, you can either use the volatile INDIRECT and be robust against row >> insertions or you can use ROW and hope you don't ever need to insert rows >> from row 1 to 99. >> >> Of course, if the date is *always* in a specific format where the length >> of the date string is *always* the same then it could be as simple as: >> >> =--MID(A1,FIND("/",A1)-n1,n2) >> >> Where n1 = 1 or 2, the length of the month portion of the date and n2 = >> the total length of the date string. >> >> This would be very easy if there was a SUBSTITUTE / REPLACE type function >> that would take arrays as the old_text argument! >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message >> news:OlSa5XdzKHA.4328(a)TK2MSFTNGP04.phx.gbl... >>> Here is a slightly different "atomic option" from the one you posted >>> which does not contain the Volatile INDIRECT function call (plus it's 2 >>> characters shorter<g>)... >>> >>> =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99))) >>> >>> -- >>> Rick (MVP - Excel) >>> >>> >>> >>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >>> news:ubzGADVzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >>>> Well, this formula has a potential flaw *if* the date is followed by >>>> another character like a punctuation mark. >>>> >>>> Maybe the "atomic option" is best afterall. >>>> >>>> -- >>>> Biff >>>> Microsoft Excel MVP >>>> >>>> >>>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >>>> news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl... >>>>> Thanks! >>>>> >>>>> But, I think I'm using an atomic bomb to kill an ant! >>>>> >>>>> That formula is a generic formula to extract a number from a string. >>>>> >>>>> I like Luke's suggestion but it needs tweaked a bit. >>>>> >>>>> Let's assume that there are no other numbers in the string and the >>>>> date is *always* in the format m/d/yyyy or m/dd/yyyy. >>>>> >>>>> Luke's formula could fail when the date is at the start of the string. >>>>> >>>>> Consider these strings: >>>>> >>>>> 1/1/2010 is the deadline >>>>> 1/10/2010 is the deadline >>>>> 10/1/2010 is the deadline >>>>> 10/10/2010 is the deadline >>>>> >>>>> The deadline is 1/1/2010 >>>>> The deadline is 1/10/2011 >>>>> The deadline is 10/1/2010 >>>>> The deadline is 10/10/2010 >>>>> >>>>> The deadline of 1/1/2010 is firm >>>>> The deadline of 1/10/2010 is firm >>>>> The deadline of 10/1/2010 is firm >>>>> The deadline of 10/10/2010 is firm >>>>> >>>>> So, Luke's formula with a tweak will account for all of the above: >>>>> >>>>> =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) >>>>> >>>>> -- >>>>> Biff >>>>> Microsoft Excel MVP >>>>> >>>>> >>>>> "Ron(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message >>>>> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com... >>>>>> Brilliant Biff >>>>>> >>>>>> "T. Valko" wrote: >>>>>> >>>>>>> Try this... >>>>>>> >>>>>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))) >>>>>>> >>>>>>> Format as Date >>>>>>> >>>>>>> -- >>>>>>> Biff >>>>>>> Microsoft Excel MVP >>>>>>> >>>>>>> >>>>>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >>>>>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com... >>>>>>> > If a cell has text and numeric (date) is there a way to pull the >>>>>>> > date out >>>>>>> > easily ? >>>>>>> > E.g. >>>>>>> > Product dropped off on 11/01/2009 by Michelle Smith >>>>>>> > I would like to see 11/01/2009 >>>>>>> > >>>>>>> > Thank you >>>>>>> >>>>>>> >>>>>>> . >>>>>>> >>>>> >>>>> >>>> >>>> >> >>
From: Rick Rothstein on 27 Mar 2010 23:14
Okay, I see what you mean. -- Rick (MVP - Excel) "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:uJwPYZhzKHA.5348(a)TK2MSFTNGP02.phx.gbl... > A1 = 1/1/2009 is the start date. > > It might be far-fetched but if you inserted 9 or more new rows at the top > of the sheet then ROW($1:$99) becomes ROW($10:$108) etc. Then LEFT starts > with 10 characters and the date portion by itself is never evaluated. > > -- > Biff > Microsoft Excel MVP > > > "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message > news:Ow02%23kgzKHA.2436(a)TK2MSFTNGP04.phx.gbl... >> Insertions do not seem to affect either of our formulas as far as I can >> see. >> >> -- >> Rick (MVP - Excel) >> >> >> >> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >> news:uzpk4FfzKHA.6140(a)TK2MSFTNGP05.phx.gbl... >>>>does not contain the Volatile INDIRECT function >>>>ROW($1:$99) >>> >>> Well, you can either use the volatile INDIRECT and be robust against row >>> insertions or you can use ROW and hope you don't ever need to insert >>> rows from row 1 to 99. >>> >>> Of course, if the date is *always* in a specific format where the length >>> of the date string is *always* the same then it could be as simple as: >>> >>> =--MID(A1,FIND("/",A1)-n1,n2) >>> >>> Where n1 = 1 or 2, the length of the month portion of the date and n2 = >>> the total length of the date string. >>> >>> This would be very easy if there was a SUBSTITUTE / REPLACE type >>> function that would take arrays as the old_text argument! >>> >>> -- >>> Biff >>> Microsoft Excel MVP >>> >>> >>> "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message >>> news:OlSa5XdzKHA.4328(a)TK2MSFTNGP04.phx.gbl... >>>> Here is a slightly different "atomic option" from the one you posted >>>> which does not contain the Volatile INDIRECT function call (plus it's 2 >>>> characters shorter<g>)... >>>> >>>> =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99))) >>>> >>>> -- >>>> Rick (MVP - Excel) >>>> >>>> >>>> >>>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >>>> news:ubzGADVzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >>>>> Well, this formula has a potential flaw *if* the date is followed by >>>>> another character like a punctuation mark. >>>>> >>>>> Maybe the "atomic option" is best afterall. >>>>> >>>>> -- >>>>> Biff >>>>> Microsoft Excel MVP >>>>> >>>>> >>>>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message >>>>> news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl... >>>>>> Thanks! >>>>>> >>>>>> But, I think I'm using an atomic bomb to kill an ant! >>>>>> >>>>>> That formula is a generic formula to extract a number from a string. >>>>>> >>>>>> I like Luke's suggestion but it needs tweaked a bit. >>>>>> >>>>>> Let's assume that there are no other numbers in the string and the >>>>>> date is *always* in the format m/d/yyyy or m/dd/yyyy. >>>>>> >>>>>> Luke's formula could fail when the date is at the start of the >>>>>> string. >>>>>> >>>>>> Consider these strings: >>>>>> >>>>>> 1/1/2010 is the deadline >>>>>> 1/10/2010 is the deadline >>>>>> 10/1/2010 is the deadline >>>>>> 10/10/2010 is the deadline >>>>>> >>>>>> The deadline is 1/1/2010 >>>>>> The deadline is 1/10/2011 >>>>>> The deadline is 10/1/2010 >>>>>> The deadline is 10/10/2010 >>>>>> >>>>>> The deadline of 1/1/2010 is firm >>>>>> The deadline of 1/10/2010 is firm >>>>>> The deadline of 10/1/2010 is firm >>>>>> The deadline of 10/10/2010 is firm >>>>>> >>>>>> So, Luke's formula with a tweak will account for all of the above: >>>>>> >>>>>> =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) >>>>>> >>>>>> -- >>>>>> Biff >>>>>> Microsoft Excel MVP >>>>>> >>>>>> >>>>>> "Ron(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message >>>>>> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com... >>>>>>> Brilliant Biff >>>>>>> >>>>>>> "T. Valko" wrote: >>>>>>> >>>>>>>> Try this... >>>>>>>> >>>>>>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))) >>>>>>>> >>>>>>>> Format as Date >>>>>>>> >>>>>>>> -- >>>>>>>> Biff >>>>>>>> Microsoft Excel MVP >>>>>>>> >>>>>>>> >>>>>>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >>>>>>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com... >>>>>>>> > If a cell has text and numeric (date) is there a way to pull the >>>>>>>> > date out >>>>>>>> > easily ? >>>>>>>> > E.g. >>>>>>>> > Product dropped off on 11/01/2009 by Michelle Smith >>>>>>>> > I would like to see 11/01/2009 >>>>>>>> > >>>>>>>> > Thank you >>>>>>>> >>>>>>>> >>>>>>>> . >>>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>> >>> > > |