If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
convert a text date to a true date
I am out of ideas. Excelâ„¢ XP on WinnXP.
I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Accessâ„¢ database. Thanks for any suggestions |
#2
|
|||
|
|||
convert a text date to a true date
I have a situation where my dates come to me as 1071114 (107 is the year, 11,
is the month and 14 is the day). I use the formula =date(left(a1,3),mid(a1,4,2),right(a1,2)) and it converts to 11/14/2007. May take a bit of modification but it should work for you. Luck "JR Hester" wrote: I am out of ideas. Excelâ„¢ XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Accessâ„¢ database. Thanks for any suggestions |
#3
|
|||
|
|||
convert a text date to a true date
Thanks Tom for that. MAybe I should be a bit more specific.
My dates are text such as Sunday, March 3, 2002 Wednesday, April 11, 2004 Friday, December 20, 2003 and so forth. "Tom" wrote: I have a situation where my dates come to me as 1071114 (107 is the year, 11, is the month and 14 is the day). I use the formula =date(left(a1,3),mid(a1,4,2),right(a1,2)) and it converts to 11/14/2007. May take a bit of modification but it should work for you. Luck "JR Hester" wrote: I am out of ideas. Excelâ„¢ XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Accessâ„¢ database. Thanks for any suggestions |
#4
|
|||
|
|||
convert a text date to a true date
If these are indeed TEXT entries you can use Text to Columns to convert
them. You'll lose the dddd portion but retain the date portion. Select the range of cells in question Goto the menu DataText to Columns Select DelimitedNextselect both Comma and SpaceNext In Step 3 the first partition should be highlighted in the data preview box. Select Do not import Finish -- Biff Microsoft Excel MVP "JR Hester" wrote in message news I am out of ideas. ExcelT XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an AccessT database. Thanks for any suggestions |
#5
|
|||
|
|||
convert a text date to a true date
Assuming your first date is in A2, put this formula in the 2nd row of a help
column... =DATE(RIGHT(A2,2),MID(A2,FIND("-",A2)-2,2),MID(A2,FIND("-",A2)+1,LEN(A2)-3-FIND("-",A2))) and copy it down through all the rows that have dates in them. Then select all the dates in this helper column and Edit/Copy (or Ctrl+C) them; then select A2 (the first cell containing the original date) and click Edit/PasteSpecial in Excel's menu bar; select Values from the option list and hit OK; then delete the helper column and format the newly copied dates however you want them to look. Rick "JR Hester" wrote in message news I am out of ideas. Excelâ„¢ XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Accessâ„¢ database. Thanks for any suggestions |
#6
|
|||
|
|||
convert a text date to a true date
On Wed, 14 Nov 2007 14:36:02 -0800, JR Hester
wrote: I am out of ideas. Excel™ XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Access™ database. Thanks for any suggestions =--MID(A1,FIND(",",A1)+2,255) Format as date --ron |
#7
|
|||
|
|||
convert a text date to a true date
Hmmm...
with dates in the dddd, m-d-yy format My dates are text such as Sunday, March 3, 2002 Wednesday, April 11, 2004 Well, I went by your first post: with dates in the dddd, m-d-yy format Meaning: Wednesday, 11/14/07 So, I don't think T to C will work on My dates are text such as Sunday, March 3, 2002 Wednesday, April 11, 2004 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If these are indeed TEXT entries you can use Text to Columns to convert them. You'll lose the dddd portion but retain the date portion. Select the range of cells in question Goto the menu DataText to Columns Select DelimitedNextselect both Comma and SpaceNext In Step 3 the first partition should be highlighted in the data preview box. Select Do not import Finish -- Biff Microsoft Excel MVP "JR Hester" wrote in message news I am out of ideas. ExcelT XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an AccessT database. Thanks for any suggestions |
#8
|
|||
|
|||
convert a text date to a true date
Given the different date format that you just posted in JR Hester's
subthread, use this formula in the help column using the procedure I outlined instead of the formula I originally posted... =--SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)),"") Rick "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your first date is in A2, put this formula in the 2nd row of a help column... =DATE(RIGHT(A2,2),MID(A2,FIND("-",A2)-2,2),MID(A2,FIND("-",A2)+1,LEN(A2)-3-FIND("-",A2))) and copy it down through all the rows that have dates in them. Then select all the dates in this helper column and Edit/Copy (or Ctrl+C) them; then select A2 (the first cell containing the original date) and click Edit/PasteSpecial in Excel's menu bar; select Values from the option list and hit OK; then delete the helper column and format the newly copied dates however you want them to look. Rick "JR Hester" wrote in message news I am out of ideas. Excelâ„¢ XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Accessâ„¢ database. Thanks for any suggestions |
#9
|
|||
|
|||
convert a text date to a true date
Rick,
this doesn't work in the UK, as March 3, 2002 is not a valid date string. I'll post my solution direct to the OP. Pete On Nov 14, 11:08 pm, "Rick Rothstein \(MVP - VB\)" wrote: Given the different date format that you just posted in JR Hester's subthread, use this formula in the help column using the procedure I outlined instead of the formula I originally posted... =--SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)),"") Rick "Rick Rothstein (MVP - VB)" wrote in l... Assuming your first date is in A2, put this formula in the 2nd row of a help column... =DATE(RIGHT(A2,2),MID(A2,FIND("-",A2)-2,2),MID(A2,FIND("-",A2)+1,LEN(A2)-3--FIND("-",A2))) and copy it down through all the rows that have dates in them. Then select all the dates in this helper column and Edit/Copy (or Ctrl+C) them; then select A2 (the first cell containing the original date) and click Edit/PasteSpecial in Excel's menu bar; select Values from the option list and hit OK; then delete the helper column and format the newly copied dates however you want them to look. Rick "JR Hester" wrote in message news I am out of ideas. Excel(tm) XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Access(tm) database. Thanks for any suggestions- Hide quoted text - - Show quoted text - |
#10
|
|||
|
|||
convert a text date to a true date
Assuming the date text is in I11:
=DATEVALUE(RIGHT(I11,LEN(I11)-FIND("^^",SUBSTITUTE(I11,",","^^", 1))-1)) Will work for all three examples cited, as well as others I can think of. The requirement is that the weekday name be followed by a comma, and the rest of the string an Excel-recognized date display. For instance, these will all work: Monday, 3/3/02 Monday, Aug 10 (assumes current year) randomtext, Aug-12 (assumes current year) randomtext, Aug 12, 2005 Will work also for differing regional setting, assuming the weekday followed by comma requirement is met. On Nov 14, 5:56 pm, JR Hester wrote: Thanks Tom for that. MAybe I should be a bit more specific. My dates are text such as Sunday, March 3, 2002 Wednesday, April 11, 2004 Friday, December 20, 2003 and so forth. "Tom" wrote: I have a situation where my dates come to me as 1071114 (107 is the year, 11, is the month and 14 is the day). I use the formula =date(left(a1,3),mid(a1,4,2),right(a1,2)) and it converts to 11/14/2007. May take a bit of modification but it should work for you. Luck "JR Hester" wrote: I am out of ideas. Excel(tm) XP on WinnXP. I have acquired a spreadsheet "database" from another user. approximayely 5K records with dates in the dddd, m-d-yy format, although these appear to be stored as text. Changing the format does not change theway data is displayed. Cells were originally formatted as General. Can anyone offer a simple formula method to change these text entries into a true date format? I am in process of importing info into an Access(tm) database. Thanks for any suggestions- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|