| Author | Message | ||
     Blazewada Celebrity Bewarse Username: Blazewada Post Number: 25891 Registered: 08-2008 Posted From: 58.182.87.144 Rating: N/A |
In your Excel worksheet, select a column of text entries you want to convert to dates. Switch to the Data tab, Data Tools group, and click Text to Columns. https://cdn.ablebits.com/_img-blog/text-date/excel-text-to-columns.png In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next. https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date.png In step 2 of the wizard, uncheck all delimiter boxes and click Next. https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date2.png In the final step, select Date under Column data format, choose the format corresponding to your dates, and click Finish. In this example, we are converting the text dates formatted as "01 02 2015" (month day year), so we select MDY from the drop down box. https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date3.png Finally, you have to combine the date parts together by using a DATE formula. The syntax of the Excel DATE function is self-explanatory: =DATE(year, month, day) In our case, <code>year</code> is in column E and <code>day</code> is in column D, no problem with these. It's not so easy with <code>month</code> because it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month's name to a month's number: =MONTH(serial_number) For the MONTH function to understand it deals with a date, we put it like this: =MONTH(1&C2) Where C2 contains the name of the month, January in our case. "1&" is added to concatenate a date (1 January) so that the MONTH function can convert it to the corresponding month number. And now, let's embed the MONTH function into the <code>month</code> argument of our DATE formula: =DATE(F2,MONTH(1&D2),E2) And voila, our complex text strings are successfully converted to dates: https://cdn.ablebits.com/_img-blog/text-date/convert-text-strings-dates-result.p ng जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के |