| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates formatted as text
Hello. I am currently having a problem with text/dates. I am importing data from another file that is completely text formatted. Some of the columns in this file contain dates in the form of "Jan-01-06," and it is always "MMM-DD-YY." I am trying to find the difference between two of these dates, but it will not work because of the text formatting. ie. A formula that would output the difference between Jan-16-06 and Jan-12-06 (the answer would be 4 days). I am wondering if there is any easy way to convert them to dates (changing format does not work). I have thought about just using the LEFT command to grab the middle two numbers for the date, but that does not take into consideration month or year changes. Please let me know if you can help. Thanks in advance! Nick -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dates formatted as text
Select one of the columns
data|text to columns fixed width (but don't draw any lines) tell excel that this field is a date (mdy) and finish up. (and do the remainder of the other columns, too.) thekovinc wrote: Hello. I am currently having a problem with text/dates. I am importing data from another file that is completely text formatted. Some of the columns in this file contain dates in the form of "Jan-01-06," and it is always "MMM-DD-YY." I am trying to find the difference between two of these dates, but it will not work because of the text formatting. ie. A formula that would output the difference between Jan-16-06 and Jan-12-06 (the answer would be 4 days). I am wondering if there is any easy way to convert them to dates (changing format does not work). I have thought about just using the LEFT command to grab the middle two numbers for the date, but that does not take into consideration month or year changes. Please let me know if you can help. Thanks in advance! Nick -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dates formatted as text
Have a look at the DateValue function.
-- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "thekovinc" wrote in message ... Hello. I am currently having a problem with text/dates. I am importing data from another file that is completely text formatted. Some of the columns in this file contain dates in the form of "Jan-01-06," and it is always "MMM-DD-YY." I am trying to find the difference between two of these dates, but it will not work because of the text formatting. ie. A formula that would output the difference between Jan-16-06 and Jan-12-06 (the answer would be 4 days). I am wondering if there is any easy way to convert them to dates (changing format does not work). I have thought about just using the LEFT command to grab the middle two numbers for the date, but that does not take into consideration month or year changes. Please let me know if you can help. Thanks in advance! Nick -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dates formatted as text
Have a look at the DateValue function.
-- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "thekovinc" wrote in message ... Hello. I am currently having a problem with text/dates. I am importing data from another file that is completely text formatted. Some of the columns in this file contain dates in the form of "Jan-01-06," and it is always "MMM-DD-YY." I am trying to find the difference between two of these dates, but it will not work because of the text formatting. ie. A formula that would output the difference between Jan-16-06 and Jan-12-06 (the answer would be 4 days). I am wondering if there is any easy way to convert them to dates (changing format does not work). I have thought about just using the LEFT command to grab the middle two numbers for the date, but that does not take into consideration month or year changes. Please let me know if you can help. Thanks in advance! Nick -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dates formatted as text
Let's say in A1 we have Jan-01-06 as a text string
In B1 put =MID(A1,5,3)&LEFT(A1,4)&"20"&RIGHT(A1,2) you will see 01-Jan-2006 While this may not seem like a big improvement, it is something that datevalue can handle. In C1 put =DATEVALUE(B1) and you will see 38718 format as date to see: 1/1/2006 You can use C1 as a real date and perform math on it. -- Gary''s Student "thekovinc" wrote: Hello. I am currently having a problem with text/dates. I am importing data from another file that is completely text formatted. Some of the columns in this file contain dates in the form of "Jan-01-06," and it is always "MMM-DD-YY." I am trying to find the difference between two of these dates, but it will not work because of the text formatting. ie. A formula that would output the difference between Jan-16-06 and Jan-12-06 (the answer would be 4 days). I am wondering if there is any easy way to convert them to dates (changing format does not work). I have thought about just using the LEFT command to grab the middle two numbers for the date, but that does not take into consideration month or year changes. Please let me know if you can help. Thanks in advance! Nick -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dates formatted as text
Thanks a lot! -- thekovinc ------------------------------------------------------------------------ thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378 View this thread: http://www.excelforum.com/showthread...hreadid=512340 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| imported text data converting to dates | Excel Discussion (Misc queries) | |||
| Reformatting dates as text | Excel Worksheet Functions | |||
| Converting Text months to sortable Numbers or Dates | Excel Discussion (Misc queries) | |||
| Converting text to dates | Excel Discussion (Misc queries) | |||
| Convert text to dates | Excel Worksheet Functions | |||
Linear Mode
