Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

How To Convert Timestamp Which Is In Text Format To Date Only?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello Everyone,

I have date like Dec 20 2012 12:00 AM which is in text format. I want it in actual date format lime 12/20/2012. I tried mid,left,right functions and then applying Date() but it shows error in that cell. But it doesn't work. Can anybody help me?


View Answers     

Similar Excel Tutorials

Convert an Incorrect Date Format into a Real Date in Excel
Change an incorrectly formatted/input date into the proper date format for Excel. This tutorial is specific to when ...
Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
Convert Time to Minutes and Vice Versa in Excel
How to convert a time into minutes in Excel and also how to get minutes back into a time format. For instance, chan ...
Get the Last Day of the Month in Excel
How to get the last day of the month, including the date and day of week, for any date in Excel.  This method allow ...
Convert Scientific Notation to Numbers in Excel
How to convert scientific numbers to show their full amount; this method also allows you to retain the appearance o ...
Format a Date to Show Only the Day of Week in Excel
How to format a Date so that only the day of the week is visible from that date. Result: Steps Right-click the desi ...

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the

Similar Topics


I have a report which shows the date in US format eg. 10-28-2007

I want to convert it to UK format 28-Oct-07 but it isn't being recognised as a date but a text.

I can use Mid(4,2)&"-"&Left(2)&"-"&Right(4) to get 28-10-2007 but it still doesn't recognise it as a date so can't be formatted as 28-Oct-07.

Any ideas,


Hi all,

I've searched a lot on Google and on here and have found a ton of ways to convert a Unix timestamp *TO* an Excel Date/Time value but I need to do it the other way.

I've got 2 columns of values (about 9700 in total) - they're all in Excel Date/Time format and the cells have been formatted as such.

I need to convert all those values *TO* a Unix timestamp. For example, one of the cells has this:

25/07/2007 9:18:08 PM

Obviously that date isn't US format (I live in AU). Anyway, the Unix timestamp for that is 1185398288 (according to

Obviously doing this manually for each cell isn't very productive.

Can anyone help me with a formula that can convert the existing Excel Date/Time *TO* a Unix timestamp?

Apologies for the emphasis but I've asked a ton of people and they all come back with how to convert the other way ...

Hi there,

With regards to a post posted at, I'm actually facing the same problem.

However, in my case, I would prefer to have the date and time as my unique ID. I've tried changing the format of the cell but it doesn't work. When a new row of data is inserted, the time and date will still be in the date format and not a text format despite changing it manually (i.e. right-click, format cell).

Thus, I would love to know if there's a way to actually convert a date format into a format whereby it turns into number, a unique ID (i.e. date: 2010/21/07 11:11 to 1021071111). It would be even better if it includes seconds.

Or if you have any suggestion as to how I can generate a unique ID without much codes, I would greatly appreciate it.

Thanks in advance!

Remedy system exports dates in Date and Time format (8/1/2009 12:19:59 PM) I want to convert this into a Month-Year format for a pivot table. I tried reformatting the column to Mon-YY and then Copy > Paste Special > Values. But it doesn't work. I can't seem to get rid of the date/time. I even tried =LEFT to try to strip the time out. But then it just gives me Excel numeric code.

Anyone have any ideas for a formula that will turn "8/1/2009 12:19:59 PM" into Aug-09?

I run into this often, and it is driving me crazy. I am querying SQL tables through MS Query. The table in the query has a field for a Due Date, which is a date format, and a field for a Ship Date, which is in a memo, or text, format. I really need to be able to convert the text field (Ship Date) to a date field so that I can filter out all of the dates that are not past due. I can CAST it as a varchar field, but can't figure out the correct syntax to then convert it to a date.

Any help is most appreciated!

I have two columns of dates in the following format :


The months and days are not fixed width, they are delimited by the slash only. For example :


are both dates in my database. These fields are text, not date values. When I try to reformat the column as a date, the cells remain text and no operations (such as sorting) can be carried out.

Is there a way of using formulas to convert the dates to actual date values, or must I use Vb ?


Hi guys - tricky one I think.

I'm using 2007 / 2010 Excel to convert multiple columns from date to text format in VBA whilst retaining the dd/mm/yyyy format.

If I use Text To Columns manually and just select text at the end (without parsing any data), it works fine. If I code this, it then converts the dates to text but in the US date order (so mm/dd/yyyy).

Any ideas about how to navigate around this?

Any advice gratefully accepted.


Hi, i was wondering if you could help me. I need to get the date into a different format.

I am copy data from a page that shows it as

2013-03-31 and i need it to read 31/03/2013

i have tried formating the cells, but that doesnt appear to work. Is there a formula i can use to convert the date into the format i need?


Have a problem with my huge file with data. In column J I have dates, like 2010-10-13, but the format of the dates is text.
Do anyone know how to make a macro to convert the entire column J into date format, and should read "13-10-2010". If I go to each cell in column J, press F2 and enter, the field automatically changes to date format, but for 16000 rows, its gonna take me alot of time.


Windows XP SP2, MS Excel 2003.

I have a project plan that has target and actual date columns.
Format is 03/02/05.

Would like to highlight the planned date cell if it is within 2 days
of todays date in yellow AND the actual date cell is blank.

If the Actual date is filled in, then no formating of the Planned date

If the planned date is = or > than today and the actual date field is
blank, format the planned date cell RED.

Any help would be greatly appreciated

Thank you


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.

Many Thanks



My ultimate goal is to sort some data in an excel spreadsheet imported from a csv file in date (chronological) order. However I've established I cant do this at the moment as although the contents of the cell is for example 01/07/2010 it is in a text or general format and not date format. So when I sort I get a list sorted by first digit only.

Is there a formula that would convert the general / text in a cell for example 01/07/2010 to a date format which could be then sorted into chronological order?

Dear everyone,

Could you please help me to convert date to height?
Date format 6/2/2000, I would like to convert it to a new cell which shows as "6'2"(or as "6.2 ft")
Date Format 5/6/2006 will show as "6.5 ft"

Thank you all so much


I have a column from an export that contains 5 digit zip codes. The numbers are stored as text. Although I formatted the column and selected Special/Zip Code, I cannot get the cells to convert to a date without actually having to click on each cell - THEN it will change. And Data/Text to Columns doesn't seem to have the option. (that only works when converting General or something else to Text).

(Am doing this because I have a separate exported report that has the zip codes in special/date format. If I convert them to text (in order to match the first report), the leading zeros go away. So my VLOOKUP will not work unless both "types" are the same. Any help would be greatly appreciated!

I'm dealing with a peculiar csv file which has a date column in which some are noted as Short Dates and others are noted as General.

Date Submitted 06/11/2007 04-20-07 04-19-07 04-19-07 04-19-07 06/07/2007 06-20-07 08-20-07
The ones noted in the DDMMYYYY format have been picked up incorrectly - the actually should be MMDDYY format.

The MMDDYY dates are in General format, so their conversion to Date format is simple.

So, for example,06/11/2007 is Jun 11th , though it has been (seemingly automatically) registered as a DDMMYYYY date.

I need to it to realise it's a MMDD(YY)YY date. When i convert it to General Text it's becomes 39392, so I cannot seem to be able to make 06/11/2007 (Date) change to 06/11/2007 (Text) either.

Basically, I need the DDMMYYYY dates to become like the MMDDYY dates. Is there a way to make them plain text and not revert to 'numeric dates' - the 'numeric dates' are wrongly becoming DDMMYYYY when they are in actual fact MMDDYYYY.

This is difficult to explain (apologies for my messy attemt), so if it's unclear please let me know and I'll try again.

I have cells with dates in the format below

Wed Jun 01 09:23:11 PDT 2011
I would like the convert the date to appear as 6/1/2011 instead of the format above. The cell above starts in cell G5 and continues down to G72. Similarly, I have two other columns from H5 and I5 with text based dates down to row 72 as well.

Thanks for any help regarding this matter.

I'm working with a linked table and the table has a timestamp field that is in a general format ex. 4/18/2007 7:00:34 PM

I need a way through a query that I can get the timestamp field to read in a short date format so I can link this table with another that is using the short date format with no time.

Any help would be much appreciated.

Thanks in advance

Hi all
Does anyone know how to get powerpivot in excel 2010 to transfer a date to the pivot table in a date format?
It want to put it in text format
I tried =format(column,dd/mm/yyyy) but doesnt seem to work
Ta in advance

I'm a long-time lurker and first-time poster.

I have a data column called "Deadline." Sometimes, the deadline is only a date, a time or both. I would like to enter only the data required (date, time or both), and I would like to format the cell as follows:

date only
ENTER: 10/1/07
FORMAT SHOWS: Mon, 10/1/2007

time only
ENTER: 5 p

ENTER: 10/1/07 5 p
FORMAT SHOWS: Mon, 10/1/2007 5:00 pm

Is there a way to set up a custom number format to do this? When I use "mmm, m/d/yyyy, h:mm am/pm" and enter the date only, it automatically adds midnight as the time.

I'd prefer to use custom number format, if possible, but I'll happily use a formula if necessary. I cannot use VBA because I have to share the spreadsheet and it's a nightmare in our company for various reasons.

Thanks in advance for any insight or help.


Hi there,

I need help converting several rows of text to dates.

For example, I have several cells in column A with text 'mmm dd yyyy' (i.e. Feb 22 2011). I need to add this data to a report with several dates but obviously when I sort the data the text dates are sorted alphabetically and the dates are sorted in date order which is no good to me. I need the report all in date order.

Does anyone know of a simple formula to convert this text format (mmm dd yyyy) into a date format such 22/02/2011?

Thanking you in advance,



I need help to convert the contents of cell A1 from American date format to European date format.

The current format is

The format I require is


I have an Excel 2007 file with 15,000 building permits from 1885 to 1945. I'm trying to clean up the data a bit. I have a column called "Date" but there's a whole mish-mash of date formats. Most of them, unfortunately, are like this: "04-Apr-1892". It appears it is as text, not in a date format.

Since I have so many in that format, any suggestions on how I can normalize those "text fields" so that "04-Apr-1892" (as text) will be "04/04/1892" (as a date)?


I'm trying to convert a column of data in date format *m/d/yyyy to a text
format without converting to serial numbers. Ie: I want to retain the
mm/dd/yyyy format. Is there a way to do this?

I should know this. I have a column (AA) with date/time in this format: 7/1/2008 12:23:01 PM
I have another column where I want the month and year so I do =(text(aa2,"mmm-dd") and for this example it shows up as Jul-08.

But when I try sort sort the column or do a pivot table it all sorts in alphabetical order. I checked the cell format for the column and it looks ok - I selected MAR-01.

What gives?

Hi All,
I have been given a spreadsheet that has been updated by a number of different users over time. The problem is the date format used by some is different then others.
I would like to have all the dates in the format DD-MON-YYYY if possible.
I tried the text to column option and 2009 May 01 would come out as 20 September 2008
after I selected DMY.
Is there any VB code that can look at each cell in a column, interperate its correct date and put it in the DD-MON-YYYY format if it is not that already?