how do I convert date format yyyymmdd to mm/dd/yyyy

how do I convert date format yyyymmdd to mmddyyy

I have rows of dates displayed as yyyymmdd (ie 20100131)

I want them displayed as regular dates (ie 01/31/2010)
0
Utf
2/10/2010 3:31:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
15889 Views

Similar Articles

[PageSpeed] 19

If it's a column of cells...

Select the column
Data|text to columns (in xl2003 menus)
choose fixed width, but don't have any delimiter lines
Choose Date (ymd)
This will convert the data to dates.

Now you can format the ranyge the way you like.

Datahead wrote:
> 
> how do I convert date format yyyymmdd to mmddyyy
> 
> I have rows of dates displayed as yyyymmdd (ie 20100131)
> 
> I want them displayed as regular dates (ie 01/31/2010)

-- 

Dave Peterson
6
Dave
2/10/2010 3:41:37 PM
I assume that you are having the data in A1 cell like the below:-

A1 cell
20100131

Paste this formula in B1 cell
=IF(A1="","",VALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)))

Place the  cursor in B1 cell and do Right Click>>Format 
Cells>>Number>>Category>>Custom>>Type - paste the below format

mm/dd/yyyy

and Give Ok.

Change the formula cell reference A1 to your desired cell, if required

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Datahead" wrote:

> how do I convert date format yyyymmdd to mmddyyy
> 
> I have rows of dates displayed as yyyymmdd (ie 20100131)
> 
> I want them displayed as regular dates (ie 01/31/2010)
3
Utf
2/10/2010 4:14:09 PM
Reply:

Similar Artilces:

How to mass convert Filename.eml to Filename.msg while keeping them in original subdirectories?
Does anyone know of a utility that walks an explorer directory and subdirectory converting every .eml file into a .msg file? My company has used Outlook express for 4 years, and we often copied emails to various Windows Explorer directories. After copying, we would often rename the .eml file -- for instance change "please help.eml" to "John Doe needs help with Outlook Express crashes.eml". Each e-mail copied resulted in an .eml file like "Hey Santa, here is my xmas list.eml" Find File .eml reveals that we have 3000 such files spread over 100 subdirectories. ...

Need help with calculating dates in records
I have a form which has a schd_to, actl_to, Dday (a field for the number of days between the schd and the actl) and other not important fields. So i'm doing the DateDiff formula to get the difference between the schd and actl dates which goes in the Dday field, but now I need for the next record in the form to take it's schd_to date and either add or subtract the previous record's Dday and update with that new date. Also, I would need for all the following schd_to dates that do not have actl_to or Dday fields filled out to update by either adding or subtracting from...

keeping format
I have an excel spreadsheet with 3 digit numbers and some lead with zeros. I went into format cell and created my own format so that I get the zeros no matter how many there is. My format is just a simple simple custom 000. Now after I get these numbers in this format I need to cancotanate(&) them to create a 6 digit number. The problem is when I cancontanate them together I loose the zeros that were added because of formating. How can I get those zeros to remain during the cancontanate process? example 916-069 then 916 | 69 then 91669 the result I am looking for is 916-069 = 916069. Hi ...

Conditional formating in Excel Charts.
Can bar chart colors be tied to and change with a given criteria (a conditional format for graphs)? I am trying to color code variations in a waterfall chart (using Excel's Bar Chart). I would like to code bars associated with positive impacts in green and bars associated with negative inpacts (not negative numbers) in red. Thanks You can incorporate this within an if statement and even make automatic by putting in the worksheet_change event of the sheet code. Sub seriescolor() ActiveSheet.ChartObjects("chart 1") _ ..Chart.SeriesCollection(1).Interior.ColorIndex = 3 E...

Conditional formatting? #3
Hello! Is it possible conditionaly to obstruct to write data in cell? For example: =IF(A2<A1;"ERR";A2) (this is circular cell) Thanks in advance. an use data validation to allow entry of data in a cell only if it conform to certain conditions. go over to Data - Validation. from the Allow list, select Whole Numbe (or Decimal). from the Data section, select Greater Than or Less Than in the resulting Minimum or Maximum section, enter either a value or cell address. you can also provide a comprehendible error message i the Error Alert tab -- Message posted from http://www.Exce...

emails without subject and date...
we recieved emails zonder sender, subject and date! but they are all usuall email and they are not virus. we have all outlook 2000 and MDeamon. any help is appriciated ...

date #4
how to set the date to appear Automaticaly, when i open excel "amir" <amir@discussions.microsoft.com> wrote in message news:89E5C8C2-11D8-4FC4-B4AB-585CFECF78EF@microsoft.com... > how to set the date to appear Automaticaly, when i open excel Appear WHERE? i want the date to appear Automaticaly,in a cell when i open excel "Gordon" wrote: > "amir" <amir@discussions.microsoft.com> wrote in message > news:89E5C8C2-11D8-4FC4-B4AB-585CFECF78EF@microsoft.com... > > how to set the date to appear Automaticaly, when i open excel > &...

Cell Format #8
Nope, didn't want to do that either (it's not actually my workbook). The best method may be to set the word wrap and then set the column height. ...

Cell Formatting #19
In Excel, I want to be able to type in a cell, superscript it, and then lock it. so later when the cell is filled out, it still has the original data in it (for example "name:") but you can type in the cell and still have the original data there. Place "name:" in its own cell then remove the border to the cell below. I believe that is the only workaround. "JDR" wrote: > In Excel, I want to be able to type in a cell, superscript it, and then lock > it. so later when the cell is filled out, it still has the original data in > it (for example "n...

Change date format #2
I have a column in excel which outputs the date as: 1/1/2005, I would like the formatting to be as such: 2005-01-01. Ther are no formats built into excel to output the dates like this. Doe anyone have any ideas how I can accomplish the yyyy-mm-dd forma -- gplevriti ----------------------------------------------------------------------- gplevritis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2568 View this thread: http://www.excelforum.com/showthread.php?threadid=39102 Hi Select your cells and press Ctrl-1 Select Custom on the number tab and select a forma...

Define Date before entering transcation
Dear All, Can I define date before entering transcation in SO POS. Ibrahim Zubair ...

vlookup(date, range,3)
Hello Group, I am doing a vlookup on a date, I will take the closest date, so have not used the last parameter. It returns the last date in the list always. Both the date in the Lookup and in the table are type = 1, a number. Am I missing something here or is it not possible to do this? Thanks, David >I will take the closest date, so have not used the last parameter. >vlookup(date, range,3) >It returns the last date in the list always. When you omit the range_lookup argument the table_array *must* be sorted by the date in ascending order. The "closest"...

Automatically put date in column when using a form
I have a spreadsheet that has 6 columns (B through G) that will be populated using Data, Form to give the input a consistant interface. I want to have the current date entered automatically in the corresponding cell in Column A so that I can run reports based on that date. I am using the Worksheet_Change (ByVal Target As Range) and it works great if I enter the data directly in the cells, but if I try to use Date, Form to enter the data, the date is not automatically put into Column A. Is there anyway to do this? I don't want the user to have to enter a key combination just to input th...

Converting address list from Outlook express to Outlook
Hi, I am trying to start using Outlook 2002 instead of the easier to use outlook express, and needed some help exporting my address list from outlook express to outlook, and aslo, wanted to be able to save a persons email address automatically in the contact folder when I reply to their email. This way I can save their email address similar to outlook express. Thanks manny In Outlook, select "File", "Import and Export", "Import Mail and Addresses", select your version of Outlook Express, deselect check boxes for mail and rules (leaving only addresses,...

Format Excel from Access
My customer wants me to transfer data from an existing Access 2000 database to Excel and email it to them. They also want specific formatting of the data. I am self taught in Access and have very little experience with Excel at this time I have successfully created the spreadsheet & transferred the data, but I am having the following problems: 1. I need to format an entire column to 3 of 9 barcode 2. I also need to format a specific cell to 3 of 9 barcode 3. I need to format certain cells to bold 4. I use TransferSpreadsheet to transfer the data & it makes the firs...

How can I insert current date into Word table cell?
In Access and Excell, one can use the "Ctrl;" or "Ctrl Shift ;" to insert the current date and time into a field. how can I do the same in an MS Word table? Use a { DATE } field. Insert>Fields -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "tmullis" <tmullis@discussions.microsoft.com> wrote in message news:0793051B-A3F8-43BF-8355-1B5E3F0BF074@microsoft.com... > In Access and Excell, o...

Conditional formating #12
how can i apply the same format to all the pages in the document without having to do it on every page? (change a word green?) <w-o-t2442squadron@live.co.uk> wrote in message news:3712bda8-5d64-43d4-a9c1-aafb32ac1beb@z25g2000vbs.googlegroups.com... > how can i apply the same format to all the pages in the document > without having to do it on every page? (change a word green?) Select and copy the entire worksheet with the right formatting. Then go to a worksheet you want to format in the same way - select cell A1, edit, paste special, Formats and hey presto. V On Dec 17, 5...

Counting cases between dates
Hi, I am using a waiting list of our clients and i would like to be able to calculate how many are on the list, from todays date, that have been waiting less than 6 weeks, 6 - 18 weeks and 18 weeks+. I would be very grateful for any help with this as it's driving me mad :( -- Many thanks, Lisa Hi Lisa Suppose you have the dates in ColB try the below 'Count of clients waiting for the last 6 weeks =COUNTIF(B:B,">" & TODAY()-(6*7)) 'Count of clients waiting for the last 6 - 18 weeks =COUNTIF(B:B,">" & TODAY()-(18*7))-COUNTIF(B:B...

Cell Format Issue #3
This is a multi-part message in MIME format. ------=_NextPart_000_0026_01CA58D2.608ADC50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In sports, there are win-loss figures. I.e. 2-0 mean 2 wins and no = losses. However, to put "2-0" is a cell and have it display as "2-0" is = not easy. Most of the time Excel decides that you are entering a date = and formats it like one. Even if I tell excel to format it as text, = Excel does not display "2-0". What is the answer, please? Dennis ------=_NextPart_000...

Chart copying messes up number formats...
I have a workbook with charts that I update each month. The data used are included in this workbook. When I finish updating the charts I select the sheets with the charts and use the copy sheet functions to create a copy of the charts to a new file. When I save the file everything looks ok with the number formats and everything. When I reopen the file I get another number format. If I open the original chart file, everything sorts out OK (!!!) and when I close it everything messes up again! I tried breaking the links (office 2003 links functions) when I still have open the original chart file,...

Money 04 convert from quicken-where's the online reconciliation?
I just switched from quicken 2003 to money 2004 and I can't find the option to reconcile my accounts to online balances - only paper balances. I have checked the owner's manual and microsoft's web help. Am I missing something? I will be back to quicken in a second if I have to reconcile to a paper statement (which my bank and brokerage no longer send). Thanks Glenn "Glenn" <uggb123@yahoo.com> wrote in message news:d3032c78.0411201836.399139fc@posting.google.com... > I just switched from quicken 2003 to money 2004 and I can't find the > option to reco...

Costumize date condition operators in a filter
I am creating a view "Anniversary next month" that should return all contacts that in the field Anniversary, the month is the next month (December). e.g. (dd/mm/aaaa) "01/12/1987", "31/12/1969" and "10/12/2000" are values that fit the condition. If there is a way to costumize the date operator in a filter, maybe we can add an opperator like "Same as Next Month" that returns all records whose month is the same as the next month. e.g. if next month = December this dates will be returned: dd-mm-aaaa 15-12-1987, 31-12-1976, 01-12-2004. Do...

How to change default date format of dd-mmm?
When I enter a date in m/dd format, e.g. "7/15",it is automatically converted to dd-mmm format ("15-Jul"). How can I change this so it stays in 7/15 format? Thanks. Ken Format the cell as m/dd from Format>Cells and click on Date and m/dd Andy. "Ken B." <kborthwick@movadogroup.com> wrote in message news:004c01c34add$96470350$a101280a@phx.gbl... > When I enter a date in m/dd format, e.g. "7/15",it is > automatically converted to dd-mmm format ("15-Jul"). > How can I change this so it stays in 7/15 format? > Thanks. Ken...

How do I convert a Power Point template to Publisher?
I have found on-line a Power Point template that I would like to copy over to Publisher. Is this possible? If so, please walk me though it. Thank you. Copy and paste. (Please tell me that you don't need step by step directions for *that*.) -- JoAnn Paules MVP Microsoft [Publisher] "Image Maker" <Image Maker@discussions.microsoft.com> wrote in message news:B98E20B1-E578-4032-B1CA-1C7867D8DB7B@microsoft.com... >I have found on-line a Power Point template that I would like to copy over >to > Publisher. Is this possible? If so, please walk me though it. ...

formating data
After importing banking information in the format "20041126000001", I want the end result to be of the format "mm/dd/yyyy". The first 8 digits will be used for this, the remainder may be deleted. Thank you for your help. Hi use the following formula in a helper column: =--TEXT(--LEFT(A1,6),"0000-00-00")) and format this cell as date -- Regards Frank Kabel Frankfurt, Germany "MLH" <MLH@discussions.microsoft.com> schrieb im Newsbeitrag news:C6D314D5-E894-470E-AB88-77570078633D@microsoft.com... > After importing banking information in the f...