Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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 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(d2,b2,c2). 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 are some examples:



Please Login or Register  to view this content.





I want to count non-blank values in column B and then in C, and record them in G and H respectively, based on comparing a date in column F to all the entries in column A. So, in G2 it is saying one member of "team A" has a value for 08/01 and H2 that 4 members of "team B" have values. The actual data sheet has 1000's of values with different number of entries per date and ranges from Aug to November. Can I accomplish this with a countA or a countif?


My sample data is as follows: A B C D E F G H 1 Date Team A Team B Date # of Team A # of Team B 2 8/1/2012 114 8/1/2012 1 4 3 8/1/2012 97 8/2/2012 2 1 4 8/1/2012 58 8/3/2012 3 4 5 8/1/2012 35 6 8/1/2012 99 7 8/2/2012 48 8 8/2/2012 97 9 8/2/2012 55 10 8/3/2012 82 11 8/3/2012 121 12 8/3/2012 55 13 8/3/2012 47 14 8/3/2012 99 15 8/3/2012 67 16 8/3/2012 78


Here is my situation. I created a database for tracking purposes that auto generates a word document with bookmarks. The bookmark items need to be in a text format. However, on two of my report to show statistics I can't figure out how to subtract the actual return date with the issue date in the text format. The are the approaches that I have tried in the update query.

DateSerial(Left([Actual Rtn Date],4),Mid([Actual Rtn Date],5,2),Right([Actual Rtn Date],2))-DateSerial(Left([Issue Date],4),Mid([Issue Date],5,2),Right([Issue Date],2))

=Format(CDate([Actual_Rtn_Date]),"Short Date"-(CDate([Issue_Date]),"Short Date")

I can get one or the other to work. If I convert the table to date format I can get the report to work but the bookmarks in the word doc won't work.

Queries are far from my strong point with access. Did some searches but all the examples I found did not correspond with what I need. Any help is appreciated.




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



I appreciate your patience as I work through explaining the scenario - I do not know if I am trying to create a three dimensional array or if it can be solved.

The spreadsheet is being used to SUM a column between two specific dates. Let's assume in the actual spreadsheet, Column A contains the list of dates beginning 1/1/2012 and continues on forever, although I am using other dates in the table below.



Column A Current Date Column B Column C Column D Column E Row 1 2/27/2012 Row 2 2/28/2012 Row 3 3/1/2012 Row 4 3/2/2012 Row 5 3/3/2012


If the current date is 3/3/2012 I need to begin the summation based on the first day of the previous month; in this example that would be 2/1/2012. So, in column B I used the DATE function

Formula:

Please Login or Register  to view this content.


which should generate:

Column A - Current Date Column B - Previous Month Column C Column D Column E Row 1 2/27/2012 1/1/2012 Row 2 2/28/2012 1/1/2012 Row 3 3/1/2012 2/1/2012 Row 4 3/2/2012 2/1/2012 Row 5 3/3/2012 2/1/2012


For the next step, lets assume Column C has the Data values which need to be totaled - they begin 1/1/2012 and run through the current date.

Column A - Current Date Column B - Previous Month Column C - units Column D Column E Row 1 2/27/2012 1/1/2012 1 Row 2 2/28/2012 1/1/2012 2 Row 3 3/1/2012 2/1/2012 0 Row 4 3/2/2012 2/1/2012 3 Row 5 3/3/2012 2/1/2012 2


What I need to solve is Column D. Column D needs to total the values from Column C based on the date range generated in Column B. So, for example D1 would be totaling the cells ranging 1/1/2012 through 2/27/2012. D2 would generate the total units from 1/1/2012 through 2/28/2012. D3 needs to generate the units over the range 2/1/2012 through 3/1/2012.

As you can see, this is presenting a challenge to me because the range selected changes based on the date. I also do not know many advanced functions and their syntax to make functions work properly.

If the end result needs multiple formulas in multiple cells to make the end result, that is perfectly fine. This is a personal workbook.

Attached is the workbook for the actual problem. I am trying to solve Columns AI and AJ on the 'Flight Time' workbook. AI and AJ are based on the sum of the numbers in column L and M respectively. AI and AJ need to SUM column L and M based on the date in Column A beginning from the first of the month, six months previous. (A date of 6/5/13 would sum from 1/1/13 through 6/5/13 and a date of 9/28/13 would need a sum from 4/1/13 through 9/28/13.)

If I can add additional information to assist in the solution please post. Thanks again.



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.





Good Afternoon from 14 degrees C about to rain Wetherby UK :-)

A third party program ( http://i216.photobucket.com/albums/c...ps4f8e9039.jpg ) is exporting date ranges in this format:
Month from 2012-05-04 to 2012-06-03. My question is please how coumd I change this: "Month from 2012-05-04 to 2012-06-03" to "May 2012"

Thanks in advance,
David

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

I have a table of expected outputs by calendar day. I want to specify a target number of outputs (user entry, cell B1), then for any given start date, calculate the end date whose elapsed period (given that start date) will yield at least that target amount of outputs.

Example follows - I need a formula (prefer not to use VBA) that will calculate the stop dates, shown in red in Column E (note: for the sake of this example, I manually determined the appropropriate stop dates an inseted them in Column E).

Sheet1 A B C D E F 1 Target output: 4 Units 2 3 4 Expected Output ( Units by Date) Date Range to Achieve Target Output 5 6 Date Units StartDate StopDate Units Yield 7 1/1/2012 0 1/1/2012 1/7/2012 4 8 1/2/2012 0 1/2/2012 1/7/2012 4 9 1/3/2012 0 1/3/2012 1/7/2012 4 10 1/4/2012 1 1/4/2012 1/7/2012 4 11 1/5/2012 0 1/5/2012 1/8/2012 5 12 1/6/2012 1 1/6/2012 1/8/2012 5 13 1/7/2012 2 1/7/2012 1/8/2012 4 14 1/8/2012 2 1/8/2012 1/12/2012 4 15 1/9/2012 0 etc 16 1/10/2012 1 17 1/11/2012 0 18 1/12/2012 1 19 1/13/2012 1 20 etc Excel 2007

Worksheet Formulas Cell Formula F7 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D7,$A$7:$A$19,"<="&E7 ) F8 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D8,$A$7:$A$19,"<="&E8 ) F9 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D9,$A$7:$A$19,"<="&E9 ) F10 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D10,$A$7:$A$19,"<="&E10 ) F11 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D11,$A$7:$A$19,"<="&E11 ) F12 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D12,$A$7:$A$19,"<="&E12 ) F13 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D13,$A$7:$A$19,"<="&E13 ) F14 =SUMIFS( $B$7:$B$19,$A$7:$A$19,">="&D14,$A$7:$A$19,"<="&E14 )


Thanks in advance!

SDL


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


I have a report, which I download on a daily basis, however, the date in column A is in a text format and shows the date in the format 20100427.

Is there anyway that I can convert this date so it shows as a date and in format DD-MMM-YYYY so today would be 27-Apr-2010




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 pasted an example of what I have. I need to have a macro be able to count cells from the first to the last in each set. This form varies each week, the only thing that is consistent is the first cell will always be A4. Thank you.

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 total

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 total

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

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!




Hi All,

I have tried most of the standard function on Excel but cannot change the format of this cell.
I have looked through this forum for the answer and have found some formulas that are similar but as I am not that good at coding I cannot make them work with my cells.

If anyone can help that would be great.
I have attached the spreadsheet.

Hi

I have a spreadsheet which is in US dates. I need to convert these into a format which excel recognises, to compare what we have on another spreadsheet which is in UK date format. For example i have 07/28/2009 in US format but also 09/11/2009, also in US format. I have tried the LEFT, MID, RIGHT rules and also DATE FORMAT. This converts the first date but keeps telling me the second is 9th Nov, when it is the 11 Sept.

When i check the formatting the first is just text but the second is date, in UK formatt. If i change them all to US then the second date is still 9th Nov (incorrect).

I have tried the text to collumns and regional settings but nothing is semms to be working. Any ideas what could be the probelm?




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!

ok, i've mistakenly make a stupid mistake in excel xp, when i type in a cell a date but i forgot to change into date format instead as a text, how do i convert it to date format without having me to type it all over again ? because i've try to changed it to format but it seem doesnt work.


Hi There.

I have imported some data from my software which includes dates also the date format what I received in excel is 1012009 although this 01-01-2009 (dd-mm-yyyy) but I failed to convert it to the date format it gives me 13-10-4670 if I applied custom format "dd-mm-yyyy" because it is considering it serial number of date, Text to column doesn't work, date, year, month, day, date value functions & custom format don't work.

any suggestion??????????????


regs,
aasim