Convert Date Looking As Text To Dates

Feb 12, 2009

I have a column wherein the dates are treated as text , i want to convert that into dates.

View 9 Replies


ADVERTISEMENT

Difficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)

May 3, 2009

look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.

View 5 Replies View Related

Excel 2007 :: Unable To Convert Text Dates To System Dates

Oct 21, 2011

I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

Method 1 : Using Text to Column Wizard (Excel 2007)

I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.

Then I press Control ~ on this data as shown in second view.

Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011

control ~ (overview)

Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786

2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.

Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261

2nd view - system dates are getting converted into 1911

Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911

Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).

View 4 Replies View Related

Convert Imported Text Dates To True Dates

May 18, 2007

I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.

Also i do not get the option to convert text to number

View 6 Replies View Related

String Of Dates Convert To From And To Date

Mar 12, 2014

I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.

Example

048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013

I want this to read

048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013

View 1 Replies View Related

Convert Dates To One Fixed Date?

Jul 16, 2013

i have messed up column A which has only dates and i have different date formats like 03/07/2013 and 07/03/2013 i need to have a fixed date for all in B like 03 July 2013

View 2 Replies View Related

Convert Non-US Dates To US Date Format

Dec 9, 2006

I am parsing a string which contains (European) dates:

"10/08/2005,11/08/2005,12/08/2005,13/08/2005,14/08/2005,".

When broken into individual cells, dates before the 13th of the month are recognised as "American" dates and display as "08/10/2005", "08/11/2005", 08/12/2005" etc. with automatic date format. Dates from the 13th onwards are left in general format as "13/08/2005", "14/08/2005".

I have written a macro that converts each date individually to its correct Excel serial number, but I can't help thinking that there must be a simpler way, and that I cannot be the first to have asked this!

View 8 Replies View Related

Convert Dates To Text ...

Sep 12, 2008

I have a columns with dates such as 10/24/2007. The next column has a count of widgets made that day.

I tried creating a column in between the two that would show only the Month and Year. I intended to base a graph on the converted dates and count, to show how many widgets were made a month. But

the graph breaks down the count to the individual days, ignoring the conversion. Is there a way I can create a column that will literally take the date number and leave only a text in its wake? So that when I slect the latter two columns, the graph will only show the months and counts in that month?

View 9 Replies View Related

Convert Dates To Text

Nov 10, 2009

I need to convert values in column A that Excel has erroneously formatted as date, to text.

4/9/5281 should be 5281049
(Excel dropped the zero in front of the 4. I need it back.)

14/9/5281 should be 5281149

View 9 Replies View Related

Macro To Convert Text 'dates'

Jul 21, 2008

I am trying to convert text 'dates' like Sat. 1/05/2007 to real dates. I need to find all 'date' cells in range B4:B100 (they are filled in yellow), strip off the offending text (i.e. Sat.), and use DateValue to turn the rest into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place.

I've spent days trying to write this macro. I've had help from experts in forums, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below.

View 10 Replies View Related

Convert Text To Dates - Cannot Group This Selection

Jun 26, 2014

I am having an issue with converting what seems like Text to Dates. I am trying to group dates in my pivot table, but I keep getting the error "cannot group this selection". My dates are left justified, causing me to believe that they are text.

When I format the cells, however, the cells say that they are already in date format. When I try using Text to Columns in the Data Tab and selecting MDY, I get the error "You cannot move a pivot table table report or insert worksheet cells, rows, or columns."

Not sure what to do from here, as I would like to group dates by weeks.

View 9 Replies View Related

Convert Dates To Text Format Using A Macro

May 16, 2007

Is there a way I can convert dates to text in a CSV file (opened via excel) using a macro?

Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns.

I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro.

The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.

View 9 Replies View Related

Excel 2010 :: How To Convert Numeric Dates Into Proper Date Format

Aug 21, 2013

I have a column of dates formatted as:

20130201

The cell format in the column is General.

I need to change it so that the format looks like this:

02/01/2013

Is there a quick/easy way to do this in Excel 2010?

View 2 Replies View Related

Convert Jd Edwards Julian Dates Format Into Standard Gregorian Date

Aug 9, 2007

how to convert the JD Edwards Julian date format into standard Gregorian date, and can it be done using VBA? Note that the JD Edwards Julian date is different than the standard Julian Date and uses 6 digits instead of 7.

View 7 Replies View Related

Extract Data With Differing Date Formats - Convert Text To Date

Mar 27, 2013

Extract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?

=LEFT(G9,SEARCH("/",G9)+7)

(The day portion of this date always has a leading zero).

View 9 Replies View Related

How To Convert Date (text String) Into Date Format

Jan 17, 2012

I have these as input entered as text:

January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010

but I need it in this DATE format:

MM/DD/YYYY

I need it as a formula to be entered into a cell in excel (not VBA)

View 9 Replies View Related

Convert A Text Date Into A Proper Date

Jan 9, 2009

I have a column of dates in thie format " January 5 03:09:36 2009" which i need to convert into a proper excel date that i can do further calculations on (adding up things, graphs etc based on dates).

Note the space before the month name. I thought about doing a left(cell,xx) to get it but that isnt going to work with the space infront and the variable length of month names.

I do not necessarily need the time included, it may be useful at some point if its easy to keep it as part of the data, if not i can live without it.

View 6 Replies View Related

Convert Date As Text To Actual Date

Jan 22, 2009

I've been sent a spreadsheet in which one column represents dates, but they have been entered as plain text, MM/DD/YYYY (ie 12/31/2008). I ultimately need to import this as a CSV into a program that only understands dates in the format DD/MM/YYYY.

I'd ideally like excel to recognise this field as containing dates, but I can't figure out how to do that, or even how to swap the days and months around as text. I'm using 2007.

View 4 Replies View Related

Convert Text Date To True Date?

Jun 14, 2012

I have some dates in text fomat on a worksheet that I need to convert them to true dates(Value). Here some sample of the text dates: '12/31/2011; Blank space12/31/2011.

Sub CnvtTextDate()
For Each Rng In Selection
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _

[code].....

View 6 Replies View Related

Convert A Date To Text?

Nov 13, 2008

If I have =TODAY() in cell A1 with the formatting ddmmyy (which returns 141108 ) how would I covert it to a number in another cell. SEE ATTACHED

View 4 Replies View Related

Convert Text To Date ..?

Feb 27, 2009

How would "April" be converted to "4/1/2009 8:00:00 AM"? The months will vary, but the time will always be 8:00:00 AM.

View 2 Replies View Related

Convert Text To Date

Jun 18, 2009

in columns I and J i have text which reads as for example 20090619. I need to convert this to a date format as in dd/mm/yyyy. A lil macro would help loads. i also have some other spreadsheets that have the date as 16 jun 2009 and i have to basically go into the cell and then hit enter for it to become formatted like the dd/mm/yyyy.

View 5 Replies View Related

Convert Date To Text

Dec 8, 2011

I am trying to convert a date to a particular format (mmm-yy) and then convert it to text.

On the spreadsheet I usually use Text(Today(),"mmm-yy") so I used the WorksheetFunction.Text to accomplish this but is there a VBA method to match this functionality?

View 2 Replies View Related

Convert Date To Text?

Mar 18, 2014

I have this kind of date:

1/11/2014 (dd/mm/yyyy)

I want to convert it to text like this: 01112014

I use =text(a1;"ddmmyyyy") but nothing happens.

View 3 Replies View Related

Convert Text To Date?

Feb 23, 2008

I've been playing around with various formulas to try and get the following to work, but doesn't do it.

What I have is a list of 'dates' in one column, but all seem to have an extra 'space' (so aren't recognised as a date). for example: 31/01/05

Essentially I'd like to convert to a date to find which is the most recent. So far i have tried to use: ...

View 9 Replies View Related

How To Convert A Text To Date

Apr 4, 2009

In the example below , i have in column A data which represent different dates, column B are the currency rate and column D has imported text figures which are not recognized by excel as format dates neither as serial codes !

Though trying to index match the column D data in column A to return the rate of that date , the result is an error ! ...

View 9 Replies View Related

Convert Text To A Date

May 10, 2007

I am looking for a formula that converts:

20070413

to:

39185 (The excel date number for April 13, 2007)

View 6 Replies View Related

Convert Text Cells To Date

Mar 19, 2009

i attached a spreadsheet with the column that needs to be converted to date. When i try to format cells i get a return of ##############. I tried changing to number and text to columns and still get the same result. I need it to look like 08/16/2008 ect.

View 2 Replies View Related

Convert Text String To A Date?

Aug 23, 2009

My database has 6 fields names and I have created the following code to capture and post data to the database form a userform. It works but I am sure there is a more efficient and elegant way to do this. The two areas I would like advice are:
1. converting the date string to a date.
2. the line of code where I subtract line6 from line4.

View 3 Replies View Related

Convert MDY To DMY With Text Version Date?

Dec 12, 2011

I have some date data use MDY format. Want to convert to DMY format. But text to columns function doesn't work as those dates are text not numbers. How can I do the convertion?

examples:

3/14/19644/18/1964

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved