Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
I need to return the oldest of three dates that are presented as text
"mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
The equation depends upon whether 030107 is
1 March 1907 or 1 March 2007 -- Gary''s Student - gsnu200783 "joeb" wrote: I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
Right - 2007 - but I also have 1995 for example.
Thanks again. "Gary''s Student" wrote: The equation depends upon whether 030107 is 1 March 1907 or 1 March 2007 -- Gary''s Student - gsnu200783 "joeb" wrote: I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
Put your text values in B1 thru B3. In A1 thru A3 enter:
=DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2)) =DATE(1900+RIGHT(B2,2),LEFT(B2,2),MID(B2,3,2)) =DATE(1900+RIGHT(B3,2),LEFT(B3,2),MID(B3,3,2)) This is what we see: 3/1/2007 030107 6/14/1993 061493 11/2/1995 110295 Finally, elsewhere in the worksheet we enter: =DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2)) and see: 061493 displayed NOTE that the formula in A1 has a different year than the formulas in A2 and A3. This is because we still need some kind of rule to give Excel so it can know if the 2 digit year refers to 1900 or 2000. -- Gary''s Student - gsnu200783 "joeb" wrote: Right - 2007 - but I also have 1995 for example. Thanks again. "Gary''s Student" wrote: The equation depends upon whether 030107 is 1 March 1907 or 1 March 2007 -- Gary''s Student - gsnu200783 "joeb" wrote: I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
Thank you for your help. I should have been more clear. Dates are in a row
and I need the oldest. Part numbers (about 3,000 of them) are in a column. Column headers a PART## DATELOC1 DATELOC2 DATELOC3 OLDESTDATE "Gary''s Student" wrote: Put your text values in B1 thru B3. In A1 thru A3 enter: =DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2)) =DATE(1900+RIGHT(B2,2),LEFT(B2,2),MID(B2,3,2)) =DATE(1900+RIGHT(B3,2),LEFT(B3,2),MID(B3,3,2)) This is what we see: 3/1/2007 030107 6/14/1993 061493 11/2/1995 110295 Finally, elsewhere in the worksheet we enter: =DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2)) and see: 061493 displayed NOTE that the formula in A1 has a different year than the formulas in A2 and A3. This is because we still need some kind of rule to give Excel so it can know if the 2 digit year refers to 1900 or 2000. -- Gary''s Student - gsnu200783 "joeb" wrote: Right - 2007 - but I also have 1995 for example. Thanks again. "Gary''s Student" wrote: The equation depends upon whether 030107 is 1 March 1907 or 1 March 2007 -- Gary''s Student - gsnu200783 "joeb" wrote: I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
I need to return the oldest of three dates that are presented as text
"mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. The oldest date is "061493" so if you could use MIN it would return "061493". "030107" is the most recent date so if you could use MAX it would return "030107". If you want the oldest date then you want the MIN date, right? We have to figure out a means of identifying the century. What is the *oldest* year in use? Are any dates older than 1970? 1980? 1985? -- Biff Microsoft Excel MVP "joeb" wrote in message ... I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date from text
Is there a reason why you cannot use the MMDDYYYY?
You can always copy the columns to another part of your worksheet, format them as date and then use the MIN function. .... "joeb" wrote: I need to return the oldest of three dates that are presented as text "mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return, if I use MAX obviously "110295" will return. Is there a way I can turn this text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to get it back to text? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert a text date to a true date | Excel Discussion (Misc queries) | |||
how do i change text format date to date (i.e., mm/yy to mm/dd/yyy | Excel Discussion (Misc queries) | |||
how do i convert text to date (mm/yy text to mm/dd/yyyy date)? | Excel Discussion (Misc queries) | |||
Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions |