converting date and time fields

Hello,
I have columns that contain date and time in the "14/07/2003  17:57:00"
format.
I need to convert them into date-only and time-only fields (not only
visually).
How do I get rid of the date/time part?
Thanks



0
neuman (4)
8/20/2003 4:05:49 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
493 Views

Similar Articles

[PageSpeed] 32

thanks!
"Laura Cook" <laura@top-brands.com> wrote in message
news:eP$BBBzZDHA.2648@TK2MSFTNGP09.phx.gbl...
> With date and time in column A, in B1 enter:
>
> =INT(A1)
>
> Format as a date.  Now in C1 enter:
>
> =A1-B1
>
> Format as a time.
>
> Copy the formulas down as far as needed.  If you no longer need column A,
> select both column B & C and Copy, then Paste > Special > Values.  Now you
> can delete column A.
>
> --
> HTH,
> Laura Cook
> Appleton, WI
>
>
> "neuman" <neuman@iname.com> wrote in message
> news:uG08fyyZDHA.2580@TK2MSFTNGP09.phx.gbl...
> > Hello,
> > I have columns that contain date and time in the "14/07/2003  17:57:00"
> > format.
> > I need to convert them into date-only and time-only fields (not only
> > visually).
> > How do I get rid of the date/time part?
> > Thanks
> >
> >
> >
>
>


0
neuman (4)
8/20/2003 5:18:43 PM
Reply:

Similar Artilces:

Problem converting from MSXML2 to MSXML4 in C#
I had a piece of code working fine with MSXML2 (it does an open get and then a send, returning the webpage of the url in the get) but my server is hosted by a company that uses MSXML4 - help? I added a reference to MSXML4 - I right clicked on my project name -> clicked Add Reference -> Went to Com tab ->Chose Microsoft XML v4.0 c: \windows\system32\msxml4.dll It asked if I wanted to replace MSXML2 and I said yes - I regret this as I think it has now screwed something up. I removed the using MSXML2; at the top of my C# and replaced it with using MSXML4; but when I try typing MSXML4 the...

Date construct with DatePart is kicking my.... well, you know.
Private Sub cmdCreatePath_Click() Dim strDateCons As String <bunch of my code that is (surprisingly) working like it should> strDateCons = DatePart(yyyy, Me.txtDateEntered) & "-" & DatePart(mm, Me.txtDateEntered) & _ "-" & DatePart(dd, Me.txtDateEntered) Debug.Print strDateCons End Sub When I click the magic button... nuffin'! I get an error message that states "Compile error: Variable not defined" and it highlights the 'yyyy' part of my first DatePart statement. What I actually wanted it to do was to take the date ...

How do I increase query field length (>20 characters) ?
I want to exclude 3 or 4 variables from a particular query. Using Not "xx"or"yy" is fine, but when excluding more than this, query fails. I think the problem is that the total text characters is quite large (approx 60 characters in total, from four exclusions). Ideas, please ? 60 characters is not the limit. 1024 characters is the limit. POST the SQL of the query that is not working (View: SQL on the menu) or at a minimum post what you are attempting as the criteria. Also "query fails" does not tell us if you get an error (what is it) or the wrong resul...

calculate swim times
Hi, How can I track swim event times - mm:ss.ms format? I need to go two digits deep in the MS format. This is to be used to track swimmer's times and chart progress. Thank you all for your time and help. Format the cells with Format/Cells/Number/Custom mm:ss.00 In article <46dGc.9460$Bv.1107548@twister.tampabay.rr.com>, "saladbar" <larrysalazar@yahoo.com> wrote: > Hi, > How can I track swim event times - mm:ss.ms format? I need to go two digits > deep in the MS format. This is to be used to track swimmer's times and > chart progre...

Every time i open a Word document, my toolbars change
is there a way to 'lock' toolbars such that they don't change every time i open a word document (new or existing)? The toolbar positions are stored in the normal template. The usual cause for such changes is that you are using Word as editor for Outlook, which has a different toolbar configuration. The simplest solution of to use Outlook's own perfectly adequate e-mail editor. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Wo...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

Changing a text field to a date field
Hi I know this is a problem for lots of people but I still cant find an answer. I have imported a text file into excel with a number field that displays credit card expiry dates as a 4 digit number. example 0408 so month + year. I have converted the number field to a text field so it doesnt drop off the first zero and now I need to convert it to a date field. Any nice easy simple ideas. Any help would be much appreaciated. Or direction to a question the same. Perhaps =DATE(100+RIGHT(A1,2),LEFT(A1,2),1) format as date -- daddylonglegs --------------------------------------------...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

my brain not comprehending the yes/no field
I have a db that enters orders. The "product" (insurance plan) has one price but 20 different "attributes" in any combo of one choice to many (plan types like medical, dental etc...) and with one "price" (premium). The plan types must show up on one line in a report with one price (they are not deliminated per plan type). The easiest way for personnel to enter all the plan types would be yes/no check boxes on the input form. My problem is I can't figure out how to make those "yes"es equal the plan name in the report! I know, it's proba...

Configuration restarts each time an Office module is opened
Hi All I am going quite mad. I have installed Microsoft Office Home and Student 2007 on one Vista notebook. Well and good. I try it on another notebook, register it successfully and now each time I open Word it tries to perform a setup again, with a Configuration Progress dialogue popping up. A few decades later Word appears. There is absolutely no indication what the problem may be. Any ideas are welcome. Cheers Andre Never mind. Problem solved. The registry needed manual editing. "J Andr´┐Ż Labuschagn´┐Ż" <technical@eduadmin.com> wrote in messa...

date #4
Hi All I live in South Africa, and I have an interesting problem that persists in Excel 2003 and 2007. =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006 =month(now()) returns 12 =text(month(now()),"MMMM") returns January =year(now()) returns 2006 =text(year(now()),"yyyy") returns 1905 Why would this be? Thanks Chris Chris, =text(month(now()),"MMMM") should be =text(now(),"MMMM") and =text(year(now()),"yyyy") should be =text(now(),"yyyy") or just =year(now()) Otherwise, you are calcing the month of the 12th day afte...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

Outlook Express Visual C++ Runtime Library Run-Time Error
I have a friend who's using Outlook Express 6 and is getting an "abnormal program termination" error whenever he attempts to attach a graphic file within Outlook Express. He reports that it works fine when using the Explorer shell to call OE. My friend has WinXP Home. Any advice? Jay ...

Converting from VS 2003 to VS 2005
I have recently converted a fully working application from VS 2003 to VS 2005. I now seem to have lost XP Themes and none of my combo boxes are working. Does anyone have any ideas? Chris "Chris Day" ha scritto nel messaggio news:20089248422chris.day@onwight.net... >I have recently converted a fully working application from VS 2003 to VS >2005. I now seem to have lost XP Themes and none of my combo boxes are >working. Does anyone have any ideas? Hi Chris, do you compile in Unicode mode in VC++ 2005? I'm not sure, but I seem to recall that VC++2005 removes new XP ...

Money 2004 Standar can't convert Money 2000 Delux File
Hi all, I just installed Money 2004 Standar on XP. I just buy It. I used to have a Money 2000 Delux version. When I try to convert my data file (from Money 200), the program makes a copy of the file with the extension "MN8" and then stops with the error: "Money cannot covert the file. The older varsion is not supported" Is there any way I can convert my Money 2000 Delux file into a Money 2004 Standar file? Thanks, Fernando S. ...

GL Posting date is missing or invalid error message
Hello, When trying to post a Sales order through Sales transactions entry window, we get an error message 'General ledger posting date is invalid or missing' The dates seem correct. The fiscal periods are all set up correctly. This happens whether we try to post an individual transcation or a batch. The batch looks like it is posting, but all the posting reports have zero mamounts in them & the batch still sits there even after posting as if it has never been posted. We are running GP 8.00g34 What is causing this & how do we fix it? Thanks Did you recently change your pos...

How can I convert Outlook 2007 Rules files (.rwz) to something editable?
I want to manage the rules a little simpler than with the built-in rules wizard. I can export the rules to a .rwz file. - Is there a description someplace of the .rwz file? - Is there a simple script or tool to convert to a .rwz file to a .csv or some other format? A perl script, VB, etc. would be fine. -- AllBackJack ------------------------------------------------------------------------ AllBackJack's Profile: http://forums.techarena.in/members/160330.htm View this thread: http://forums.techarena.in/ms-office-support/1277903.htm http://forums.techarena.in ...

not enough memory or disk space to display or print the picture after 1st time
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I know this topic has been covered before, but not in the way I get the problem. I can open a document with a math formula or a graphic the FIRST time and it will correctly display. Any changes to the document or any subsequent openings I then get the error message, &quot;not enough memory or disk space to display or print the picture&quot;. <br><br>eMac 1.25 Gz 10.4.6, Word 2004 <br><br>Thanks. One possible cause: You're *way* behind on Tiger -- it's been at 10.4.11 for ...

CRM Time Error
Hi, The dates stored in the DB in sql Server is diffent, from when you browse it throghe IE. Correct dates shows in IE, but not in SQL DB. I am generating a Birthday Reminder which is not coming in the correct date since time in DB is Incorrect. Please Help..... Irshard Zahir Hi Irshard, The dates in the database are converted to the local date of the user. The problem can be tackeled by using the CRM SDK. Hopefully this works if not please let me know. Regards, Ron "Irshard" <Irshard@discussions.microsoft.com> wrote in message news:9E7A1EC9-87E7-4B8D-8F0F-B7CFE9...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

How to Clear the AutoDetect Cache for the To: field
HI, When sending a message in Outlook, one begins to type the destination address in the To: field. Generally speaking if one has sent email to this address in the past then Outlook will auto detect and provide a suggested list of reciepients (know what I'm talking about???). Anyway, I know this feature can be disabled so one is never provided with a list of possible receipients but does anyone know how to clear this cache???? Thanks Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key ...

How to force the user to fill in values for yes/no fields ?
Hi ! I'm using MS Access 2003. I have a table with a number of yes/no fields. When a new record is started, I want the user to explicitly specify a value for each of these fields, e.g. by choosing one of two radio buttons associated with the field. Now, it was no problem for me to arrange an option group with two suitable radio buttons for each of these fields. The user can then choose explicitly between yes and no. However, all fields seem to have implicit values immediately after a new record has been started. Therefore, the new record can be stored in the db without the user making...

Convert hh:mm:ss to seconds?
I am inputting two times as h:mm:ss and subtracting them. I am trying to get the final answer as seconds. The only thing I can think of is trying to use LEFT and RIGHT to separate hh, mm and ss, multiply to convert the hh and mm to seconds, and then add to get total seconds. One - the LEFT and RIGHT don't seem to want to work for me - I keep getting decimal answers, even with a hh:mm:ss format. Two - is there an easier way? Ed Hi Ed Multiply by 86400 and format as decimal. HTH. Best wishes Harald "Ed" <ed_millis@NO_SPAM.yahoo.com> skrev i melding news:uxdZXHozFHA...

How to stop outlook resending attachments multiple times
When I send an email with an attachment, it will send several times before I manually have to stop it. Does anybody have any suggestions on how to rectify the problem? "fisco" <fisco@discussions.microsoft.com> wrote in message news:F793C1E7-AE4D-45F7-BCA6-98C570F2F915@microsoft.com... > When I send an email with an attachment, it will send several times before > I > manually have to stop it. Does anybody have any suggestions on how to > rectify > the problem? Sorry, but this newsgroup is for questions about Access, the database product tha...