| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Date
Hi- I have been read all instruction to convert to date but none of them
working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
Select the range of dates which needs to be corrected. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will take you to Step 3 of 3 of the Wizard. From Column Data format select Date and select the date format in which your data is (DMY).Hit Finish. MSExcel will now convert the dates to the default date format of your computer. If this post helps click Yes --------------- Jacob Skaria "MN" wrote: Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
"MN" wrote:
I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
On my copy of Excel 2002 that only works if the string of digits are 8
characters long. It works for this: 11022005 It doesn't work for this: 1102005 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Select the range of dates which needs to be corrected. From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will take you to Step 3 of 3 of the Wizard. From Column Data format select Date and select the date format in which your data is (DMY).Hit Finish. MSExcel will now convert the dates to the default date format of your computer. If this post helps click Yes --------------- Jacob Skaria "MN" wrote: Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
Thank you all for reply ...
The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ? "JoeU2004" wrote: "MN" wrote: I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
"MN" wrote:
The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. More accurately, it is in the form of mddyyyy. The salient difference is: the months 1-9 do not have a leading zero, and your number is not always 8 digits. Consequently, we cannot use LEFT(A1,2) and MID(A1,3,2) to exact the month and day. I am ass-u-me-ing that 2/3/2009 is represented by 2032009. Note the leading zero before the day (03). Right? If that's the case, then try: =DATE(RIGHT(A1,4), LEFT(A1,LEN(A1)-6), MID(A1,LEN(A1)-5,2)) Be sure the column is wide enough for 10/30/2009 (10302009). If you see "###", it is not wide enough. Tried enter 1302005 it become 1/1/1904 ? I don't see how you got that result. When I use my previous formula, which is incorrect for 7-digit "data codes", I get the bogus result 1/2/2006. That's to be expected since LEFT(A1,2) would use 13 for the month. What cell is 1302005 in? And exactly what formula did you use to get 1/1/1904 (copy-and-paste from the Formula Bar)? If you got 1/1/1904, I suspect: (a) you have the 1904 Date System option set, or you are using a Mac; and (b) the cell that appears as 1/1/1904 actually contains a zero. You do realize, don't you, that you should replace A1 in my formula with a reference to whatever cell contains 1302005? And IMHO, there is no good reason to set the 1904 Date System option (Tools Options Calculation) if you are using a PC (i.e. non-Mac). Some people suggest it for dealing with negative elapsed time. I think it's a bad idea. (No good reason, that is, perhaps unless you intend to use the xls file directly on a Mac. I don't know beans about swapping files between the two OSes.) ----- original message ----- "MN" wrote in message ... Thank you all for reply ... The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ? "JoeU2004" wrote: "MN" wrote: I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
This might work:
=--TEXT(A1,"00\/00\/0000") Works on 7 or 8 digit strings -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "MN" wrote: The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. More accurately, it is in the form of mddyyyy. The salient difference is: the months 1-9 do not have a leading zero, and your number is not always 8 digits. Consequently, we cannot use LEFT(A1,2) and MID(A1,3,2) to exact the month and day. I am ass-u-me-ing that 2/3/2009 is represented by 2032009. Note the leading zero before the day (03). Right? If that's the case, then try: =DATE(RIGHT(A1,4), LEFT(A1,LEN(A1)-6), MID(A1,LEN(A1)-5,2)) Be sure the column is wide enough for 10/30/2009 (10302009). If you see "###", it is not wide enough. Tried enter 1302005 it become 1/1/1904 ? I don't see how you got that result. When I use my previous formula, which is incorrect for 7-digit "data codes", I get the bogus result 1/2/2006. That's to be expected since LEFT(A1,2) would use 13 for the month. What cell is 1302005 in? And exactly what formula did you use to get 1/1/1904 (copy-and-paste from the Formula Bar)? If you got 1/1/1904, I suspect: (a) you have the 1904 Date System option set, or you are using a Mac; and (b) the cell that appears as 1/1/1904 actually contains a zero. You do realize, don't you, that you should replace A1 in my formula with a reference to whatever cell contains 1302005? And IMHO, there is no good reason to set the 1904 Date System option (Tools Options Calculation) if you are using a PC (i.e. non-Mac). Some people suggest it for dealing with negative elapsed time. I think it's a bad idea. (No good reason, that is, perhaps unless you intend to use the xls file directly on a Mac. I don't know beans about swapping files between the two OSes.) ----- original message ----- "MN" wrote in message ... Thank you all for reply ... The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ? "JoeU2004" wrote: "MN" wrote: I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
Thank you it work !!!
"JoeU2004" wrote: "MN" wrote: The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. More accurately, it is in the form of mddyyyy. The salient difference is: the months 1-9 do not have a leading zero, and your number is not always 8 digits. Consequently, we cannot use LEFT(A1,2) and MID(A1,3,2) to exact the month and day. I am ass-u-me-ing that 2/3/2009 is represented by 2032009. Note the leading zero before the day (03). Right? If that's the case, then try: =DATE(RIGHT(A1,4), LEFT(A1,LEN(A1)-6), MID(A1,LEN(A1)-5,2)) Be sure the column is wide enough for 10/30/2009 (10302009). If you see "###", it is not wide enough. Tried enter 1302005 it become 1/1/1904 ? I don't see how you got that result. When I use my previous formula, which is incorrect for 7-digit "data codes", I get the bogus result 1/2/2006. That's to be expected since LEFT(A1,2) would use 13 for the month. What cell is 1302005 in? And exactly what formula did you use to get 1/1/1904 (copy-and-paste from the Formula Bar)? If you got 1/1/1904, I suspect: (a) you have the 1904 Date System option set, or you are using a Mac; and (b) the cell that appears as 1/1/1904 actually contains a zero. You do realize, don't you, that you should replace A1 in my formula with a reference to whatever cell contains 1302005? And IMHO, there is no good reason to set the 1904 Date System option (Tools Options Calculation) if you are using a PC (i.e. non-Mac). Some people suggest it for dealing with negative elapsed time. I think it's a bad idea. (No good reason, that is, perhaps unless you intend to use the xls file directly on a Mac. I don't know beans about swapping files between the two OSes.) ----- original message ----- "MN" wrote in message ... Thank you all for reply ... The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ? "JoeU2004" wrote: "MN" wrote: I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text to Date
"MN" wrote:
Thank you it work !!! Good to hear. You're welcome. But I hope you saw Biff's (T Valko). It is much more elegant and efficient without sacrificing intuitiveness. ----- original message ----- "MN" wrote in message ... Thank you it work !!! "JoeU2004" wrote: "MN" wrote: The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. More accurately, it is in the form of mddyyyy. The salient difference is: the months 1-9 do not have a leading zero, and your number is not always 8 digits. Consequently, we cannot use LEFT(A1,2) and MID(A1,3,2) to exact the month and day. I am ass-u-me-ing that 2/3/2009 is represented by 2032009. Note the leading zero before the day (03). Right? If that's the case, then try: =DATE(RIGHT(A1,4), LEFT(A1,LEN(A1)-6), MID(A1,LEN(A1)-5,2)) Be sure the column is wide enough for 10/30/2009 (10302009). If you see "###", it is not wide enough. Tried enter 1302005 it become 1/1/1904 ? I don't see how you got that result. When I use my previous formula, which is incorrect for 7-digit "data codes", I get the bogus result 1/2/2006. That's to be expected since LEFT(A1,2) would use 13 for the month. What cell is 1302005 in? And exactly what formula did you use to get 1/1/1904 (copy-and-paste from the Formula Bar)? If you got 1/1/1904, I suspect: (a) you have the 1904 Date System option set, or you are using a Mac; and (b) the cell that appears as 1/1/1904 actually contains a zero. You do realize, don't you, that you should replace A1 in my formula with a reference to whatever cell contains 1302005? And IMHO, there is no good reason to set the 1904 Date System option (Tools Options Calculation) if you are using a PC (i.e. non-Mac). Some people suggest it for dealing with negative elapsed time. I think it's a bad idea. (No good reason, that is, perhaps unless you intend to use the xls file directly on a Mac. I don't know beans about swapping files between the two OSes.) ----- original message ----- "MN" wrote in message ... Thank you all for reply ... The data enter as a number (?) 1302005 for 01/30/2005 my data are in the format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ? "JoeU2004" wrote: "MN" wrote: I have a cell format as general like: 1102005 I want to convert to format I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) Well, it might help if you looked at the Excel help page for DATE and passed the parameters in the correct order. The parameters are DATE(year,month,day). Doesn't matter what format you want (see later). So try, assuming you have a typo and the date really looks lik 11022005: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) and use the custom format mm/dd/yyyy if you want 01/01/2005, for example. Or use the Date format if you want 1/1/2005. Caveat: If you typed the date correctly above and it should be interpreted as 1/10/2005, post back for more instructions. That is, is the date always 8-character text with leading zeros for the month and day? Or is the entered as a number, which means that 1/1/2005 will appear as 1012005? ----- original message ----- "MN" wrote in message ... Hi- I have been read all instruction to convert to date but none of them working so please help: I have a cell format as general like: 1102005 I want to convert to format mm/dd/yyyy I did try DATE, and DATEVALUE function but not working? Like: =DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4))) or =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4)) :-(( Thanks you inadvange |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| date in text format as a real date | Excel Worksheet Functions | |||
| convert a text date to a true date | 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 | |||
Linear Mode
