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?


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

View 9 Replies


Convert Different Date Formats Into One Date Format?

Apr 15, 2013

I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.









Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY. Can I have formula to convert different date formats into one date format?

View 9 Replies View Related

Macro To Convert Date Formats In Two Columns And Compare

Sep 15, 2014

I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.

View 8 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:


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

View 9 Replies View Related

Date Formats In A Cell Which Also Contains Text

Nov 29, 2013

I need to change the date format in a cell which also contains text.

The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")

This gives me the following:-
Report period:
01 October 2012 - 30 September 2013

What I would like to have is the following:-

Report period:
1st October 2012 - 30th september 2013

It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?

View 4 Replies View Related

Excel 2013 :: Extract Date And Time Info From A Text Cell That Contains Text And Date And Time

Jul 19, 2014

I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.

THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.

I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.

View 7 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:= _


View 6 Replies View Related

Extract Date From Text

Dec 26, 2013

Any formula that can extract from a time stamp text string, which looks as follows: 7/8/2013 7:10:33 AM.

The tricky bit is saying anything before the first "/" represents the month, anything between the 1st and 2nd "/" represent the date, and the 4 digits after the second "/" represents the year.

View 9 Replies View Related

Extract Date From Text

Dec 15, 2008

I have some dates with text, I want to extract dates from the text. Here is an example.

AB1ORIGINALI WANT2PREVIOUSLY CAPITALIZED 3Capitalize 09/05 per FMS 9/30/20054Capitalization date 11/05 11/30/20055PREVIOUSLY CAPITALIZED 6Capitalize 12/05 per FMS 12/31/20057Capitalize 08/05 per FMS 8/31/20058PREVIOUSLY CAPITALIZED 9Capitalize 09/05 per FMS 9/30/200510Capitalize 12/05 per FMS 12/31/200511CAPITALIZE 12/2006 12/31/200612PREVIOUSLY CAPITALIZED 13PREVIOUSLY CAPITALIZED 14Capitalize 09/05 per FMS 9/30/200515Capitalize 03/05 per FMS 3/31/200516Capitalize 12/05 per FMS 12/31/200517PREVIOUSLY CAPITALIZED, CAPITALIZE 12/06 12/31/2006

Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Convert Date & Time As Text To Real Date & Time

Apr 22, 2008

I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.

View 2 Replies View Related

Extract Latest Date To Particular Date From Different Date List

Mar 9, 2014

i have several styles to handle mentioned under different rows separately.

all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y

now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC

View 1 Replies View Related

Extract AM / PM As Text From Date / Time?

Oct 19, 2010

I have a column of times, that are actually dates of 1/0/1900 plus time, from which I want to extract the AM or PM status. I can format it to show this, but it's really just a fraction of a day. I need the actual text "AM" or "PM", not a decimal value.

Is there a formula to do this?

View 9 Replies View Related

Extract Date & Time From Text

Aug 9, 2007

i currently have a table/template in which external data is pasted... however there are 2 colums titles DATE and START TIME (amongst others) now although where the data is pasted from the information is in the correct format. After the information is pasted both the columns have exactly the same information in them. I'm pretty sertain the only reson for this is that the source the records/data actually comes from formats them on the screen differently to how the records are stored on the database using code....

so after the expanation which hopefully made sense is there any way i could convert a record like this

Thu Jun 28 21:54:33 GMT 2007 (this is how the information is pasted into the 2 columns)

to both

28/06/2007 for the DATE column
21:54:33 for the START TIME column

the cell format makes no difference because the information is both text and numbers....

View 9 Replies View Related

Extract Date From Varying Text

Feb 14, 2008

I am trying to extract dates from text strings of varying lengths. The date may appear anywhere in the string. The dates are all 8 characters, in the mm/dd/yy format.

For example:

12/12/2007 is the date in this string.
There is a date of 12/12/2007 in this string.
The date in this string is 12/12/2007.

View 3 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:



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

View 6 Replies View Related

Extract Latest Date From Wall Of Text?

Mar 27, 2014

Lets say I have a wall of text that looks like this, all of it in 1 cell.

[Code] .....

What formula do I use to extract the latest date from this string? In this example, the ideal result will be 02/05/2014 .

View 5 Replies View Related

How To Extract Month From Date Which Is In Text Format

Nov 8, 2012

I have a column with the following values:


November 8, 2012 1:37:07 PM GMT+05:30

November 8, 2012 9:32:03 AM GMT+05:30

November 8, 2012 4:32:54 AM GMT+05:30

November 7, 2012 10:28:11 PM GMT+05:30

November 7, 2012 8:36:43 PM GMT+05:30

November 7, 2012 6:33:13 AM GMT+05:30


I need another column, with just the month specified there based on above dates, so that I can manipulate the complete sheet further using a pivot. Some formatting issue isn't allowing me to use month() function, to derive the month of the date mentioned in the cell.

Using Auto-filter is too tedious, since I'd have to do it 12 times to cover each of the month. Tried recording a macro, but that too isn't supporting much, since it gets specific to cells and hence isn't re-usable in another sheet with similar data.

How can I automate the task of extracting just the month from the above column?

View 4 Replies View Related

Extract Text From Cell (Time And Date)

Mar 23, 2007

In Cell A1 Value is: 20070322_023047_002035_1112223333

above code is like Date(YYYYMMDD)_Time(HHMMSS)_Agent#_Phone#

I want result As per Following:

In Cell B1: 03/22/2007 Date(MM/DD/YYYY)
In Cell C1: 02:30:47 Time([HH]:MM:SS)
In cell D1: 002035
In cell E1: 111-222-3333

View 10 Replies View Related

Convert Imported Date / Time Data To Date / Time Format?

Jan 2, 2013

I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).

View 4 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

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