Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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?

Thanks,
Manish

View Answers     

Similar Excel Tutorials

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 ...
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 ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...

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









Good morning,

I have one that's tricking me up a bit. I have some data that is presented in a date code of 201201012354 or (Jan. 1, 2012 at 23:54) and I'm trying to convert the string of numbers to a 01/01/2012 23:54 format but my =text formula has not been working. Anyone have any suggestions that might work.

201201010354 I want to equal 01/01/2012 03:54
201201010454 " " 01/01/2012 04:54

I've tried using =Text(B1,"0000-00-00-00:00")+0 but that has returned the "Value" error.

Thanks for any help you can provide.



I am having trouble figuring out how to sum up the # of overlapping days with lots of date ranges.

I am using Excel 2010 and this is some of my data.
1/14/2012 1/14/2012
1/14/2012 1/14/2012
1/15/2012 1/20/2012
1/16/2012 1/20/2012
2/4/2012 2/5/2012
2/4/2012 2/11/2012
2/4/2012 2/12/2012
2/4/2012 2/13/2012
2/4/2012 2/16/2012
2/11/2012 2/24/2012
2/11/2012 2/24/2012
3/16/2012 3/17/2012
3/24/2012 4/1/2012
3/25/2012 4/1/2012
3/26/2012 3/27/2012
4/21/2012 4/30/2012
4/21/2012 5/5/2012
4/21/2012 5/5/2012
4/22/2012 5/8/2012

I need to add up all the networking days and exclude holiday. Was wondering if there is an easy way to calculate this.

Thanks,

Tuan

I have a spread sheet laid out as shown in second example below. I need to create another table with the value for from this first column for each date in the respective row. Each row can have a different amount of dates up to 365 dates in a row. All dates are flused left in the table so the first blank cell in a row indicates end of dates in that row.
should end up like looking like the first example below....I have 2500 rows to convert.

LDG-473 4/3/2012 LDG-473 7/2/2012 LDG-471 9/1/2011 LDG-471 11/30/2011 LDG-471 2/28/2012


LDG-473 4/3/2012 7/2/2012 LDG-471 9/1/2011 11/30/2011 2/28/2012 5/28/2012 8/26/2012 LDG-470 8/15/2011 11/13/2011 2/11/2012 5/11/2012 8/9/2012 LA-HI6M 7/20/2011 1/16/2012 7/14/2012 LA-IHYPM 8/10/2011 8/9/2012 LDG-476 7/1/2011 9/29/2011 12/28/2011 3/27/2012 6/25/2012 LDG-475 7/1/2011 9/29/2011 12/28/2011 3/27/2012 6/25/2012 LDG-472 7/1/2011 9/29/2011 12/28/2011 3/27/2012 6/25/2012 LDG-BP5 9/1/2011 11/30/2011 2/28/2012 5/28/2012 8/26/2012 LDG-730 7/4/2011 7/3/2012 LDG-BP1 1/4/2012 4/3/2012 7/2/2012 LDG-BP2 6/15/2011 9/13/2011 12/12/201 3/11/2012 6/9/2012 9/7/2012 LDG-BP3 2/10/2012 5/10/2012 8/8/2012 LABMHO3 11/12/2011 2/10/2012 5/10/2012 8/8/2012 LABMHO2 6/15/2011 9/13/2011 12/12/2011 3/11/2012 6/9/2012 9/7/2012 LDG-332 6/16/2011 9/14/2011 12/13/2011 3/12/2012 6/10/2012 9/8/2012




I have imported a heap of date from a crystal report and in excel i get dates and times as follows:

19/10/2012 3:35:00AM 19/10/2012 4:04:00AM
22/10/2012 3:50:00AM 22/10/2012 4:20:00AM
23/10/2012 3:50:00AM 23/10/2012 4:19:00AM
24/10/2012 3:38:00AM 24/10/2012 3:57:00AM
25/10/2012 3:24:00AM 25/10/2012 3:54:00AM
26/10/2012 3:38:00AM 26/10/2012 4:11:00AM
27/10/2012 5:35:00AM 27/10/2012 6:04:00AM
28/10/2012 6:40:00AM 28/10/2012 7:09:00AM
29/10/2012 3:39:00AM 29/10/2012 4:09:00AM
30/10/2012 4:03:00AM 30/10/2012 4:31:00AM

I need to convert the time from the text cells into Excel time so I can graph the time differences.
Can anyone offer a simple way to do this as i have mountains of data.

Cheers, Matt



Hi team,

I have been sent a spreadsheet from an external source.

The date is supplied to me in text format i.e. 2012-08-14 08:29:33.207

I would like to convert this format to simply dd/mm/yyyy
How do I go about doing this?

TIA!
Talan

Hi,

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,

thanks
Steve




I am editing a CSV file to be loaded into a UI. The date/time format needs to be changed. I have formatted my column using this custom format => yyyy-mm-ddThh:mm:ss
Then entered the first few intervals of data for a pattern for Excel to recognize. I select a section then attempt to drag and fill the remainder of the cells.

What happens is the seconds get incremented by one, instead of the minutes being incremented by thirty. What is really weird is yesterday this did 'almost' work. After a random range the seconds would decrement by one. Which was okay because there is a round formula to correct.

What I really want is to take the following set and have it increment by 30 minutes and the date increment as it should. This data will span one year.

2012-07-01T00:00:00
2012-07-01T00:30:00
2012-07-01T01:00:00
2012-07-01T01:30:00
2012-07-01T02:00:00
2012-07-01T02:30:00
2012-07-01T03:00:00
2012-07-01T03:30:00
2012-07-01T04:00:00
2012-07-01T04:30:00
2012-07-01T05:00:00

Thanks



Hi everyone,

I have been looking for a solution on various forums for a few days on and off but have not yet found a solution.

I have a column with dates in, some of which are prefixed with ^, some not. For example:
Cell A2 shows 07/01/2012
Cell B2 shows ^07/01/2012

When I pivot table the report I get odd results as not all cells pick up as dates.

As a solution I use Text To Columns, followed by =date(cellref,cellref,cellref). With a Copy / PasteSpecialValues I get exactly what I need.

However, when I record these actions and run the macro back, I get a result with a mix of UK and US date formats. For example, some convert as 07/01/2012 whilst some convert as 01/07/2012. Both are valid dates, but they are completely different.

If anyone can help me to either remove the ^ from the cells that have it, or correct my macro so that it keeps all dates in the UK (dd/mm/yy) format, I'd be very grateful.

Below is the section of the code I am using. I have attached a short extract of the data.

Thank you all!



Please Login or Register  to view this content.



Excel 2007

I have a data source that provides dates as text. When I manually change the format to "date" I do not get the desired result -- it is still text. Could I get some help with code to convert the text to actual date format?

Thank you


Hi

Basically I am importing a list of dates from a webpage - they import like this:

28.8. 2011
10.9. 2011
18.9. 2011
24.9. 2011
01.10. 2011
15.10. 2011
23.10. 2011
29.10. 2011
05.11. 2011
19.11. 2011
26.11. 2011
03.12. 2011
10.12. 2011
17.12. 2011
21.12. 2011
26.12. 2011
31.12. 2011
02.1. 2012
14.1. 2012
21.1. 2012
31.1. 2012
04.2. 2012
11.2. 2012
25.2. 2012
03.3. 2012
10.3. 2012
17.3. 2012
24.3. 2012
31.3. 2012
07.4. 2012

I need them in this format: 2011/08/28

I have attached an example file. Don't mind if the correctly formatted dates display in a new column or sheet.

Ultimately I would then like to do this to several different "team sheets"

The sample import link is http://www.soccervista.com/team.php?teamid=10260

The table is at the bottom of that page headed - Upcoming matches

I would really appreciate your help!

Many thanks



Hi, I trying to import external data (from Quickbooks) into Excel using Microsoft Query. I filter TxnDate using Between [Start Date] and [End Date] in parameter (Criteria Value). I tried to enter so many different formats in parameter value such as: 12-15-2012, 12/15/2012, 2012-15-01, etc but shows the same error "Conversion Error". I looked up on the table detail (QuickBooks), the format TxnDate is Date, Length 10, Validate: cccc-mm-dd.
I also tried to change the Criteria Value to be: >= [Start Date] and <= [End Date], still the same problem.
It works if I enter sample date such as #12-15-2012# in Criteria value. I really appreciate if there is anyone who can help me to solve this problem. Thanks so much!

I have a date that's in text format as follows:

Fri 8/8/2008 4:11 PM

Excel doesn't recognize this as a date unless I cut off the "Fri " part of it (including space).

I want to convert this to a custom date format: [$-409]ddd h:mm AM/PM;@
to show the date as "Fri 5:51 PM". But, I can't apply the custom date format to my value, since it's just a text string.

I have hundreds of these (various dates and times), and don't want to manually delete the first 4 characters of the value (e.g., "Fri "). Is there a way to cut these characters out quickly so that all my values turn into dates, which can then be re-formatted using my custom date format?

Thanks!




I am unsure of where to start, so here's my problem:

Here is a sample of the beginning data:

Date Price
5/1/2012 122.12
5/2/2012 121.89
5/3/2012 123.27
5/4/2012 121.48
5/7/2012 121.79
5/8/2012 123.35
5/9/2012 124.47
5/10/2012 125.2
5/11/2012 123.29
5/14/2012 122.57
5/15/2012 120.4
5/16/2012 119.62
5/17/2012 118.23
5/18/2012 117.17
5/21/2012 119.11
5/22/2012 120.3
5/23/2012 125
5/24/2012 125.52
5/25/2012 126.43
5/29/2012 129.54
5/30/2012 128.81
5/31/2012 130.25


I want the program/solver/formula (whatever i need) to start with the earliest date (5/1/2012) and take the beginning price (122.12) and see if there is a higher value in the next 4 (business) days.

If there is a higher value, then the program should move to that date. This pattern will continue until there is not a higher value in the next 4 (business) days.

If there is not a higher value, the program needs to calculate a slope test (y2 -y1)/(x2-x1) for each value for the next 14 (business) days.

For example, the slope for 5/10 and 5/11 is -1.91. The program should only care about slopes =< .02 and => -.02. Therefore, the program would move from price to price until it stopped on 5/23 where the slope between 5/10 and 5/23 is .02.

Finally, once it has found a slope that meets the parameters, it should output--in text--the dates and prices used in finding the slope, and the dates and prices of the next 3 days after the value of y2, x2 (5/23).

For example:
the output should be:
5/10/2012 125.2
5/23/2012 125
5/24/2012 125.52
5/25/2012 126.43
5/29/2012 129.54

Any help would be greatly appreciated; I've been stuck for days.

Thank you!



I have pasted an example of what I have. I need to have a macro be able to subtract the first cell from the last in each set. The sets will constantly change, and I do not know how to tell it to look for the empty cell and use the cell above it to subtract from 1st cell each group - make any sense? Hoping someone can help me out. Thanks.

6/4/2012 18:56:06
6/4/2012 19:31:01
6/4/2012 19:57:28
6/4/2012 20:15:54
6/4/2012 20:53:31
6/4/2012 21:14:21
6/4/2012 21:37:53
6/4/2012 21:53:26
6/4/2012 22:44:11
6/4/2012 22:54:58
6/4/2012 23:32:45
6/5/2012 00:10:24
6/5/2012 00:25:55
6/5/2012 01:06:16
6/5/2012 01:15:28
6/5/2012 01:39:18
6/5/2012 01:58:37
6/5/2012 02:41:43 Need this cell to subtract from 1st cell of this group.

6/5/2012 18:55:58
6/5/2012 19:19:28
6/5/2012 21:22:40
6/5/2012 21:29:36
6/5/2012 22:59:33
6/5/2012 23:04:39
6/6/2012 00:40:38
6/6/2012 00:45:10
6/6/2012 00:49:59
6/6/2012 00:55:55
6/6/2012 01:04:38
6/6/2012 01:09:57
6/6/2012 01:50:54
6/6/2012 01:56:00
6/6/2012 02:05:30
6/6/2012 02:19:17
6/6/2012 02:28:50
6/6/2012 02:40:26
6/6/2012 02:59:53 Need this cell to subtract from 1st cell of this group.

6/6/2012 18:13:04
6/6/2012 18:24:29
6/6/2012 18:34:49
6/6/2012 18:40:05
6/6/2012 18:46:03
6/6/2012 18:47:51
6/6/2012 19:02:13
6/6/2012 19:17:10
6/6/2012 19:19:28
6/6/2012 19:21:26
6/6/2012 19:44:08
6/6/2012 19:58:40
6/6/2012 20:14:04
6/6/2012 20:30:40
6/6/2012 20:58:00
6/6/2012 21:19:04
6/6/2012 21:47:06
6/6/2012 22:05:41
6/6/2012 22:39:04
6/6/2012 23:05:01
6/6/2012 23:29:46
6/6/2012 23:40:19
6/6/2012 23:50:57
6/7/2012 00:19:36
6/7/2012 00:28:48
6/7/2012 00:32:02
6/7/2012 00:37:52
6/7/2012 00:47:54
6/7/2012 00:53:48
6/7/2012 01:06:11
6/7/2012 01:08:52
6/7/2012 01:16:45
6/7/2012 01:31:19
6/7/2012 01:47:14
6/7/2012 02:31:53 Need this cell to subtract from 1st cell of this group.

I've been looking for answer to this one for awhile. I have a bunch of files with cells that contain a date in the date format (i.e. 10/15/08 stored as "39736" in excel). My task is to create a macro that converts these excel dates to static text in the format yyyy-mm-dd. I've been trying to look for a function online and in the VBA help that will do this. No luck! I keep finding format functions or "=text("A1", yyyy-mm-dd) but both keep the information stored as a date data type. I want to convert the actual data type to text while formatting the text into yyyy-mm-dd. If anyone can help me with this it would be GREATLY appreciated. I was thinking maybe formating the date to yyyy-mm-dd and then convert to raw text....but I don't know.




Hi everyone,

I am working through a data problem at the moment where I am trying to retrieve values based on the time they occurred. The issue is that the times are not always very uniform and there are 15-100 time entries per day across several years worth of days (several hundred thousand rows).

It looks like this essentially:

Date Time Value
1/01/2012 00:07:01 25
1/01/2012 01:07:01 10
1/01/2012 02:07:01 15
1/01/2012 03:07:01 60
1/01/2012 04:07:01 35
1/01/2012 05:07:01 34
1/01/2012 06:07:01 22
1/01/2012 07:07:01 58
1/01/2012 08:07:01 15
1/01/2012 09:07:01 11
1/01/2012 10:07:01 12
1/01/2012 11:07:01 13
1/01/2012 12:07:01 14
1/01/2012 13:07:01 18
1/01/2012 14:07:01 72
1/01/2012 15:07:01 64
1/01/2012 16:07:01 24
1/01/2012 17:07:01 654
1/01/2012 18:07:01 54
1/01/2012 19:07:01 91
1/01/2012 20:07:01 82
1/01/2012 21:07:01 64
1/01/2012 22:07:01 9
1/01/2012 23:07:01 22

The values I need to retrieve are as follows:

1. Value closest to 07:00 for each day
2. Value closest to 18:00 for each day
3. Minimum value before 07:00 for each day
4. Maximum value after 07:00 for each day

The times may be in very different intervals (i.e. 4-5 minutes) or more evenly spaced as shown above, it changes throughout.

If anyone could help or has any ideas for a best practice approach it would be greatly appreciated!!!

Thanks!

Edit: Example document attached.

Example.xlsx

I want to convert a column of UK date to US date. I understand that this can be done easily using e.g =Text(A1,"mm/dd/yyyy")

However, the problem is that in that original UK date column, there are mixture of date format and cell format. e.g.

25/12/2009 (text format)
31/12/2009 (date format)
12/2009 (text format, mm/yyyy)
2009 (text format, yyyy)

I like to create a function that is intelligent enough to convert the full UK date dd/mm/yyyy (regardless whether text or date cell format) to full US date mm/dd/yyyy, but skip doing the conversion for dates in mm/yyyy or yyyy formats.


Hi,

I have a column of dates from a downloaded source. Some are aligned to the right of the cell, some to the left (no apparent pattern/consistancy).

The ones aligned to the left of the cell (I think) are text values because when I use =MID(A1,4,2) it extracts the number I expect and I can mess around with text functions to rearrange these dates.

However, the ones aligned to the right of the cell I think are formatted as date values and so text functions do not work correctly

Example (not a great example I know but all the ones which are aligned to the right have the middle part of value 12 or less, making it seem like that's a month value - but it's not)

Date = 12/12/2013
MID(A1,4,2) returns a value of 20 which clearly isn't 12!

I've tried to manually change the format of the date cell to a UK format by using custom (DD/MM/YYYY) but that doesn't work.

Anyone encountered this problem before and if so, what's the solution? Is there a general formula or procedure I can apply to the whole column to make them read as UK dates?

Thank you,
Jack


Hey experts - need some help with dates.
I have received a speadsheet with Dates e.g. 04/01/2008 in Column A. The Dates have been sent to me in the format dd/mm/yyyy. The issue is that the actual date in Column A is Apr 1, 2008 not Jan 4, 2008. I need to convert this date to show up in my report as Apr 01. I tried the following but failed
- Converting the date to mm-dd-yyyy format using Custom format. Did not work - it only changed the display but not the basic date
- Using a MONTH function, it shows up as Jan and DAY function shows up as 04.
- Tried using the LEFT string function but it doesn't work - it extracts the digits off the date serial number.

Any suggestions - thanks in advance!
Mike


Hi

I am having problems subtracting dates. Ideally I would like to use the NETWORKDAYS function but the problem I am having is that the file is imported from Oracle and some of the dates include both the date and actual time, for example, 02/01/06 14:20:44. There are 2 spaces between the date and the time. I have tried a combination of the LEFT and LEN functions to remove the time but because this is a text function, it converts all the dates to text. You then need to convert them back to numbers and apply a date format. I am hoping someone can come up with a quick way of changing a date such as 14/06/06 15:23:24, to 14/06/06 so that this is the actual value that shows in the Formula Bar.

Thanks

Shirley Munro


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 http://www.onlineconversion.com/unix_time.htm).

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 http://www.ozgrid.com/forum/showthread.php?t=52378, 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!

I have a TextBox in a UserForm in which the user puts a date in any format of his choice. The routine checks that it is a recognisable date and then proceeds. In some cases I want to convert this date, in the TextBox, to the date of the last day in the month. I can do that. I can either specify the month-end date format to be used (e.g. =Format (dMonthEndDate, "dd-mmm-yyyy"), or, if I do not, I automatically get an American date ("mm-dd-yyyy") which is useless here in England.

My question is this: Is it possible, please, to pick up the date format in which the original date was entered by the user so that I can return the month-end date in the same format?

tia.


I am trying to display both text and a date in the same line. Currently I have something like

="The Date Is " & A1

where A1 is a cell with a date formatted in a preferred format. However, once I call reference to the cell, excel re-formats it into the serial date, so it displays something like

The Date Is 40220

instead of something like

The Date is 2/11/2010

Does anyone know how to format the number back into a date form? Just going through the format menus and setting it to date does not change it. Thanks for your help


I have a huge file with all sorts of dates ranging from around 1998 to 2020. The fields are in the format 31.01.2001 and they are in text format. They cannot be converted using the format cells option, to date format. I depserately need to somehow convert the thousands of rows of dates into (1) excel date format, and (2) US date format at that.

I've tried breaking out the day, month and year and concatenating them all back together again in an effort to brute force excel into thinking it was a date. Perhaps unsurprisingly to no avail.

Please help me! Thank you so much in advance.