Date conversion #6

I have a database with date in the following format:
"200209021600" so 2 september 2002 16:00 hr

I cannot figure out how to convert this text to the actual date and time

Who can help me?

Thanks in advance

Kristiaan

0
7/30/2005 12:05:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
450 Views

Similar Articles

[PageSpeed] 55

Hi Kristiaaan,

This formula works for me:

=--(TEXT(A1,"0000-00-00 00\:00"))

Don't forget to format the cell as date.

Regards,
KL


"Kristiaaan" <Kristiaaan@discussions.microsoft.com> wrote in message 
news:4E445E7D-7B2C-4CDE-939B-DC35EAF6CBBE@microsoft.com...
>I have a database with date in the following format:
> "200209021600" so 2 september 2002 16:00 hr
>
> I cannot figure out how to convert this text to the actual date and time
>
> Who can help me?
>
> Thanks in advance
>
> Kristiaan
> 


0
7/30/2005 12:14:45 AM
On Sat, 30 Jul 2005 02:14:45 +0200, "KL" wrote:
>This formula works for me:
>
>=--(TEXT(A1,"0000-00-00 00\:00"))
>
>Don't forget to format the cell as date.

KL-  What are the two hyphens for at the beginning of this formula?

MP-
--
"Learning is a behavior that results from consequences."
 B.F. Skinner
0
manguspyke (36)
7/30/2005 1:06:08 AM
They are coercing text into value as if I were using the function VALUE.

Regards,
KL


"Mangus Pyke" <manguspyke@REMOVE-TO-REPLYcomcast.net> wrote in message 
news:5jkle15qct1tj3n78pnk47bibq06eddkag@4ax.com...
> On Sat, 30 Jul 2005 02:14:45 +0200, "KL" wrote:
>>This formula works for me:
>>
>>=--(TEXT(A1,"0000-00-00 00\:00"))
>>
>>Don't forget to format the cell as date.
>
> KL-  What are the two hyphens for at the beginning of this formula?
>
> MP-
> --
> "Learning is a behavior that results from consequences."
> B.F. Skinner 


0
7/30/2005 1:10:45 AM
On Sat, 30 Jul 2005 03:10:45 +0200, "KL" wrote:
>They are coercing text into value as if I were using the function VALUE.

KL:

Thanks for the response.. while I don't truly understand, that gives
me a starting point.  My Excel 2003 Bible and I will go spend a few
moments of quality time with the VALUE function.

Thanks much!

MP-
--
"Learning is a behavior that results from consequences."
 B.F. Skinner
0
manguspyke (36)
7/30/2005 2:26:28 AM
Hi KL,

Wow, I couldn't make that up myself. You really helped me out, thanks!
Now I can finally make a nice graph of my data... let's see what it tells 
me...

Regards,

Kristiaan

"KL" wrote:

> Hi Kristiaaan,
> 
> This formula works for me:
> 
> =--(TEXT(A1,"0000-00-00 00\:00"))
> 
> Don't forget to format the cell as date.
> 
> Regards,
> KL
> 
> 
> "Kristiaaan" <Kristiaaan@discussions.microsoft.com> wrote in message 
> news:4E445E7D-7B2C-4CDE-939B-DC35EAF6CBBE@microsoft.com...
> >I have a database with date in the following format:
> > "200209021600" so 2 september 2002 16:00 hr
> >
> > I cannot figure out how to convert this text to the actual date and time
> >
> > Who can help me?
> >
> > Thanks in advance
> >
> > Kristiaan
> > 
> 
> 
> 
0
7/30/2005 4:49:02 AM
Reply:

Similar Artilces:

Excel Date Format
I am creating an Excel spreadsheet that goes into next year. I have set the date format as: Mar-04. Every time I enter 05, it automatically changes to 04. However, if I enter only the first two letters of the month (Ma-05), it takes it. This occurs even when I continue to increase the margin. Can anyone help me, please? Thanks, Julie On Thu, 24 Jun 2004 13:58:50 -0700, "Julie" <anonymous@discussions.microsoft.com> wrote: >I am creating an Excel spreadsheet that goes into next >year. I have set the date format as: Mar-04. Every time >I enter 05, it ...

Locking Dates in Excel
I am entering the current date in cells using the Now command Unfortunately that date updates as the date changes. How can I use a formula to eneter a date then have it fixed so that i does not change. : -- Message posted from http://www.ExcelForum.com Hi instead of using =now() you could use the short cut keys of ctl & ; for the date and ctrl & shift & ; for the time these are static and won't change. Cheers JulieD "jamesmac >" <<jamesmac.1bls3b@excelforum-nospam.com> wrote in message news:jamesmac.1bls3b@excelforum-nospam.com... > I am enterin...

date format setting
Hi, I'm trying to accomplish through some VBA code: read from a flat text file (fixed width) some information to be put in an Oracle table through ODBC. Nothing special. But the text file has some "date fields" in the format "dd-mmm-yyyy", i.e. the 4th of March reads "04-MAR-2008. Some of the date values are however not correctly imported into the table, leaving the field in question blank after import is finished. I am in the Netherlands, and I suspect that the problem comes from a difference in "date-format" setting between my MsAccess front end (ver...

public folder replication problem #6
Exchange Server A - sp1 with W2k3 sp1 Exchange Server B - sp1 with W2k3 sp1 Server A was the first server, then Server B. I added Server B as replication partner for all 469 public folders that we have. All PF were created but many of them had not contents. It appeared that all the folders were created but not contents were actually replicated. So, I ran pfmigrate again to delete Server B as replication partner and log said it was successful. I did this so I could start over. But, when I look at Admin Groups>First Admin Group>Server B>First Storgage Group>Public folders>...

MS OutLook 6 (98)
S.O.S. I've sent from work 2 to 3 e-mail to my home e-mail with safffe attachments, these are now shown as inaccessible and the message"OE removed access to the following attachments in your e-mail: abc.pdf". I've check the OutLook help files but can'r find anything about this new security filter. I know these attachments are safe but I can't get to them!? Thx, Cannot Open E-Mail Attachments in Outlook Express After You Install SP1 http://support.microsoft.com/?kbid=329570 OLEXP: Using Virus Protection Features in OLEXP 6 (OE removed access to the unsafe attac...

sum between to dates from a pivot table...
I have this huge file with employment data. I want to make sure i have enough employees at the time when we have lots of customers, now when the vacations coming up. I have made a pivot table of the names and the work times of the employees every day, to the right of it i made a graphical presentation of their work times ciontaining formulas and custom formatting so i easily see where i need more people, i don't always have the same number of employees at work. I would like for my model to sum up the employees to though (as shown red text in the yellow field in the pic). The dates are fixe...

Input date format
Hello, I would like to format cells in a m/d/yyy format. However I want to be able to input the date without the slashes to save time for the data entry person. For example I want to input 2344 and have excel convert it to 2/3/1944. I have used the format cells feature and set it to date m/d/yyyy, and it gives that format as long as I use the slashes, but gives a bogus date with slashes when I type the numbers as a string. Is there a way to do this? Chip Pearson has some code that'll do what you want at: http://www.cpearson.com/excel/DateTimeEntry.htm But I think you'll want ...

Date formatting #10
Hi: I am trying to mail merge a column of recertification dates to June 2006 only. But every way I try to format this, it is has unusual responses. I even tried formatting June 2006 as text but this does not work. I want the actual response in the merge document to actually show "June 2006" but excel keeps updating each entry into the next date. Any ideas? Thanks Ron What information is on the Excel sheet? Maybe you need to add another column with a formula that evaluates to "June 2006". If you are doing something similar now, what formula are you using? What progra...

datetime conversion
Hi All, I have a excel report in which I will get timestamp datatype as a string and not datetime,which is not a required field and may be null too. if I pass 20090331221501, can I convert it to 2009-03-31@22:15:01. ? Thanks, Roy =LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & MID(A1,7,2) & "@" & MID(A1,9,2) & ":" & MID(A1,11,2) & ":" & RIGHT(A1,2) -- Gary''s Student - gsnu2007L "Roy" wrote: > Hi All, > > I have a excel report in which I will get timestamp datatype as a > ...

How to build a project in Visual C++.net developed in Visual C++ 6
Hi, I am Nageshwar. I have developed a project in VC++ 6.0 and OpenGL. I have installed VC++.net in my system. When I compile and build the same code in VC++.net environment, I am getting errors. I have used fstream.h to read and write to files. But this header file is showing errors in VC++.net. I have used iostrea.h header. This is also showing erros. I think I need to use a different version of headers. Please suggest me how to recover from these errors. Which header file I have use instead of fstream.h header? etc., Thanks for all who help me.. Awaiting an early response, Nageshwar ...

Invalid count date error.
I keep getting this error anytime I try to run "Calculate" in Physical Inventory count. What am I doing wrong? I am new to MS RMS so please bear with me. What this means is that there are entries on your count sheet that have a date that is less than the date of the Physical Count itself. To fix this what you can do is open the count and right-click any where in the grid then choose Show/Hide Columns and put a check mark next the Count Date column and press OK. Change any of the entries that have a date less than the Date Opened field. Now try and Calculate the count. Rob &qu...

Cheque printout to a file with date & amount converted to text/string
Print cheque output a text file, and this text file will be sent to bank for further processing. There are some fields are hardcoded and some are from GP fields, including date and amount. We modify the Report Writer for the Cheque printing, the added a few of calculated fields. Some calculated fields will contain text with hardcoded data (may contain special characters, like comma and /) Some calculated fields will hold data from the field found in GP and if it is date format, it will be using RW_DateToString() to convert it to string text and this calculated can be showing in...

Excel Date Multiply Problem
Hello. I have billing dates and a billing frequency number that I nee Excel to generate the upcoming billing dates for. Example: The original billing date is 10/30/03 which is in cell C2 an the billing frequency is 1 (for monthly) -or- 3 (for every thre months) which is in cell M2. How would I get Excel to return the value of 11/30/03 if the frequenc was 1 (monthly)? How would I get Excel to return the value of 01/30/04 if the frequenc was 3 (every three months)? Your help is greatly appreciated. Tony in Las Vega -- Message posted from http://www.ExcelForum.com There are some tools in...

Sent date = Received date in Outlook 2000??
I have a problem with Outlook 2000 SP3 on Win 2000. When mail is received on some computers, the sent date and time is set to the current date and time on the computer. This means, that I can not see when the mail was sent - because the correct date and time seems to be overwritten by outlook when receiving. This only happens when Outlook has been closed for a while, and opened again. When Outlook is open and mail is received, the correct date and time is displayed. When I right click on the mail and view properties, I can see the correct date and time on all mail - but it is not all display...

Sample Access Database using Oracle Data and needing Time/Date Sta
Does anyone know of a good web site out there that has some sample Access Databases that invoke and use Oracle Data or even SQL Server for that matter that also uses a Time/Date Stamp driven Map to drive and filter a query to get results back via an ADO call??? I have been asked to create an Access Database as a GUI Type application which will utilize an Access Form and require User Input to Enter a Date and Time which will ultimately filter the data. I have no idea where to begin on something like this. (Boy....that sounds like a tall order....) Any help would be GREATLY...

Outlook Express version 6
I have a Dell Precision 340 computer attached to a windows network. I have been running this machine for a year now. Just yesterday when a click on the Microsoft express icon the splash screen comes on and just hangs. No new software was loaded. When I go to task manager it does not even show outlook as an apllication that is running. I download the ie6setup and tried to reinstall but that did not help. any ideas? Thanks. Joe bashed at the keyboard and said : > I have a Dell Precision 340 computer attached to a windows > network. I have been running this machine for a year now...

Microsoft Office #6
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Ok this may seem like an odd question but as an archaeology student I kinda really really need this but I cannot find the button that lets you centralise text and such...where is it???????????? Without any idea what program you're using or what you mean by "centralise text and such" it's impossible to answer your question. I'm afraid I've never come across such a command -- let alone a button for it. Regards |:>) Bob Jones [MVP] Office:Mac On 10/17/09 8:43 AM, in article 59b7ec7f.-1@web...

Filter By Date Range
All, I have a large spreadsheet of data with several columns one of which contains dates. Is there a way I can add a start date in A1 and a finish date in B1 and then filter out the data to only show lines which fall between between and including those two dates. I have tried using advanced filters but this only seems to work for single dates or by having to type all the dates in the range (a bit unuser friendly) I'm sure there a way but I can't figure it out. Sadly the date ranges I would like to use are quite obscure i.e 15/07/2005 - 08/08/2005, so I cant just filter by month ...

date calculation #6
hi i have a database where column b is date column c is customer name when i input customer name in the system it should check if the same customer name appears within 15 days of time from his last transaction. -- Nisha P so, if I understand correctly, you enter the date in column B, thencustoemr name in C. Once you do this, you want to be told if the customer has been added within 15 days of the date you just entered? If so: =if(C200="","",if(countif(B$2:B199,">=C200-15")>0,"Added within 15 days","Not Added")) Should work ...

Noob Question CString to Float conversion
Hey guys - I am having difficulty in converting a CString to a float value to allow me to do calculations with it. Any help would be appreciated. Thanks. try atof() "Rik Irving" <indigoed@btinternet.com> wrote in message news:bpqv0g$qv8$1@titan.btinternet.com... > Hey guys - I am having difficulty in converting a CString to a float value > to allow me to do calculations with it. > > Any help would be appreciated. > > Thanks. > > Rik Irving wrote: > Hey guys - I am having difficulty in converting a CString to a float value > to allow me to ...

Date conversion: SELECT goes well, WHERE fails.
Hello, So I have spent now couple of hours tearing my hair out about this problem. So, I have this set of data - table with string field in format 'DDMMYY-ABCD', where DDMMYY points to the date of the document. Sample field value would be 100610-47234. I want to filter the data by this date, so I need to convert it to date value. This is how I do it: SELECT .. CONVERT(datetime, '19' + SUBSTRING(documentCode, 5, 2) + '-' + SUBSTRING(documentCode, 3, 2) + '-' + SUBSTRING(documentCode, 1, 2), 120) documentDate SELECTing this in query analyzer ...

conditional formatting
I would like to use conditional formatting to highlight cells. Here is the scenerio .. a cell has a date that is calculated from a formula. If that date falls within the next 7 days i would like the cell to change colors. I for the life of me can not figure out the correct formula to put into the conditional formatting box. Thanks in advance. One way: With A1 selected: CF1: Formula is =AND(A1>=TODAY(), A1-TODAY()<=7) In article <0ED02BFE-5813-464A-9DCE-E902D6C974B9@microsoft.com>, Dan <Dan@discussions.microsoft.com> wrote: > I would like to use condit...

Web Query date parameters
Hi, I have numerous individual web queries maintained in spreadsheets that I need to update bi-monthly. It works fine except that each time the month changes I have to go in and revise the date for the query paramaters to coincide with that in of the server. I tried developing a macro but the macro would not recognize the queries as individual entities thus it fixed the date but kept the data from the first sheet throughout the remaining sheets. I also tried going into options and disabling date recognition but it did not work either. I something in data range properties that might work? I...

Build date and time
I inadvertantly had my machine date one month ahead for a short time and now VS wants to re-build my application everytime I change anything or want to execute it. I have reset every file date I can see but it still happens. Where does it store the date/time that determines whether the project is up to date ? > Where does it store the date/time that determines whether the project is > up to date ? Make should only need to refer to the file time and system time... I would the a Clean and Rebuild all... > I would the a Clean and Rebuild all... I think he is trying to avoid j...

Sort a range of Dates on a subform
I have a Form and a Subform where I have datas and every record has a field named: Wdate also I have a field where I put a date I would like to press a button to sort equal or less dates than the date that I put on the field, please help Thanks in advance -- Lorenzo Díaz Cad Technician On Sat, 23 Feb 2008 08:28:00 -0800, ldiaz <ldiaz@discussions.microsoft.com> wrote: >I have a Form and a Subform where I have datas and every record has a field >named: Wdate >also I have a field where I put a date >I would like to press a button to sort equal or less dates than ...