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


Convert Number (YYMMDD) To Actual Date Indicated

Jun 3, 2013

Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?

View 5 Replies View Related

Text Date To Actual Date?

Nov 26, 2013

I inport date into excel and the dates come in APR/13, however excel does not sort or treat the imported dates as actual dates. if I select the cell it will change the data to 13-Apr-2013...however I want it to be the 1st of the month.

Via a formula how can I change the text from APR/13 to 1-Apr-13, so that excel will sort and treat the value as a date?

View 2 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?


(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:


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

Date Serial Number To Actual Date

Nov 30, 2007

I have a macro which copies the values from one cell to another. But, while copying the macro changes the date format to a serial number and this doesnot happen all the times. Below is the code I have. I want the macro to automatically convert the date from from serial number to dd/mm/yyyy. I tried quite a different ways, but none of them seems to work. Below is the macro I run to copy the contents from one cell to another.

Sub History()
' Macro: Backup results from prior rounds. Please run this macro after selecting the cell where the backup is to be done.
cur_row = ActiveCell.Row
If ActiveCell.Offset(-(cur_row - 10), 0).Value <> "History" Then
MsgBox ("Check whether the active cell is the designated history cell. History operation failed")
Exit Sub
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & ActiveCell.Offset(0, -5).Value & " "..........................

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

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

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?



View 9 Replies View Related

Convert Numeric Text To Date

Sep 6, 2012

I have some dates in Excel in different formats and I need to convert them all to a uniform date format of MM/DD/YYYY.

The data is in this format:


The days of the month don't really matter. It's the month and year that I need in a date format.

View 2 Replies View Related

Convert Text Comments To Date In VBA

Nov 8, 2012

I have a situation where textual comments are entered into a cell where the comments are supposed to begin with a date in mm/dd format. Some enter all 5 characters but some enter m / d in if appropriate to the date, e.g., 9/6 vs 09/06.

I'm trying to take the left 5 characters and compare that to the current date - 7 to ID aged comments and highlight that cell. I currently do this by populating a variable LastUpdate (dim as string) with the first 5 characters, then run this IF statement.

If LastUpdate < Date - 7 Then
Range("O" & i).Select
Selection = "Comments over 7 days old."
End If

This works find except in situations where there is a zero for the first character after the "/", such as "11/05." The code doesn't properly calculate the date and assumes it's old. "11/5" works just fine. I thought I could try translating the variable into a proper date. I "borrowed" this formula from another post herein: =DATE(2012, MID(N12,1,FIND("/",N12)-1), MID(N12,FIND("/",N12)+1,2)). This works fine as a formula to translate the characters into a date but I don't know how to do this same thing in vba.

View 5 Replies View Related

VBA - Convert Date To Text Format

Jun 9, 2014

i am looking for vba macro to convert date format (indonesian language) to be text format, see this below :

sample (col.a)
after vba

09 Oktober 2014

26 Desember 2011

05 Juni 2010


View 6 Replies View Related

Convert Date Format To Text

Nov 13, 2006

I am trying to make a very simple macro but get stuck in de Excel date format. When using the date "=TODAY()" in one cel it is no problem but I want to integrate it in the CONCATENATE function.

I want to create this text "(WG 01-01-2006)" but then with the actual date of that day of course, which can be inserted in de active cell.

Whatever I do, copy, pastespecial, reed text with RIGHT, change format on an temporary cell etc. Excel keeps returning the number in Excel date format counting from 01-01-1900. Nice for calculating but I want the date text!

View 9 Replies View Related

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

Convert Date To Text Day Number?

Sep 17, 2006

Wondering if there is a simple and concise way to convert a date into text that represents the day number?

So a cell containing “17/09/2006” simply reads as “Seventeenth”, 18/09/2006 becomes “Eighteenth” and so on...

View 3 Replies View Related

Convert Date To Text Format

Oct 16, 2007

Is there a way to convert date to text of format (number stored as text) either directly using excel formulas or VB macros. We've a requirement to pass today's date as a string in the format of "number stored as text". For this, i've in cell A1 a formula today() which gives today's date 10/15/2007 and I'm looking an output of '20071015 in cell A2 and this should be treated as text with a symbol " ' " before 20071015.

View 2 Replies View Related

Convert Long Date And Time Text To Value

Oct 4, 2013

I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.

I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39".
Otherwise i get a type mismatch error.

Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?

Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.

I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.

View 2 Replies View Related

Convert Text String Into Excel Date With VBA

Aug 13, 2013

I have a date range that looks like this (British date style, not US):

edit: I posted an image but it can't be seen for some reason:

Date Range
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10

Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):

edit: I posted an image but it can't be seen for some reason:

Week Commencing

I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.

Sub ConvertTextToBritStyleDate()
' dd/mm/yyyy (not mm/dd/yyyy)
' Select the cells containing the dates (not the header)


View 3 Replies View Related

Date To Return The Actual Day

Jan 13, 2009

how to get a date to return what the actual day? ie 13/01/09 is tuesday?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved