Need help converting date time to date only

I have a column in my spreadsheet with date and time, you can see in m
attachment. I want to convert this column in to date only. Pleas
help.

Thanks
Geng

       Attachment filename: sales - open orders test.xls       
Download attachment: http://www.excelforum.com/attachment.php?postid=57210
--
Message posted from http://www.ExcelForum.com

0
6/9/2004 10:36:26 PM
excel 39879 articles. 2 followers. Follow

2 Replies
611 Views

Similar Articles

[PageSpeed] 48

Hi
without looking at your file you could use the following formula in a
helper column
=INT(A1)
and format this cell as 'date'

--
Regards
Frank Kabel
Frankfurt, Germany


> I have a column in my spreadsheet with date and time, you can see in
> my attachment. I want to convert this column in to date only. Please
> help.
>
> Thanks
> Genga
>
>        Attachment filename: sales - open orders test.xls
> Download attachment:
> http://www.excelforum.com/attachment.php?postid=572108 ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
6/9/2004 10:45:07 PM
Or, if you need it as a text field,
=text(a1,"mm/dd/yy") . Lots of flexibility in the
format.
"Frank Kabel" <frank.kabel@freenet.de> wrote in
message
news:%23O2NGNnTEHA.2416@TK2MSFTNGP12.phx.gbl...
> Hi
> without looking at your file you could use the
following formula in a
> helper column
> =INT(A1)
> and format this cell as 'date'
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > I have a column in my spreadsheet with date
and time, you can see in
> > my attachment. I want to convert this column
in to date only. Please
> > help.
> >
> > Thanks
> > Genga
> >
> >        Attachment filename: sales - open
orders test.xls
> > Download attachment:
> >
http://www.excelforum.com/attachment.php?postid=572108 -
--
> > Message posted from http://www.ExcelForum.com/
>


0
UncleBob (6)
6/10/2004 5:42:33 PM
Reply:

Similar Artilces:

how do i find a true value between two different times e.g. 4:15
i would like to find the exact value between two different time in hours and minutes e.g. 4:15 (4 hours 15 minutes) and not 4:42 (4.42 hours) for example. Subtract them! An example: Enter 8:45 in A1 and 5:15 in A2. Enter the formula =A1-A2 in A3 and, voila, the answer is 3:30. Excel will automatically assign the time format to A3 if it has the General number format. -- Jim "Chris01623" <Chris01623@discussions.microsoft.com> wrote in message news:CF16BD54-CC0C-4188-B18D-1C3CE871D95A@microsoft.com... |i would like to find the exact value between two different time in h...

Format date in excel 2000 like '31st January 2005' #4
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Good afternoon Malcolm Agingwell Would the date in the format "31 January 2005" be of any use to you? If so, set up a custom format of dd mmmm yyyy. Excel doesn't handl ordinals (1st, 2nd, 3rd etc) on its own. HTH Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getin...

Date changes back 4 years when copying
When we copy a date from an excel 2000 worksheet and paste it in an excel XP worksheet the year will move back four years. Any ideas? Go Tools > Options > Calculation and make sure both files have '1904 Date System' either checked or unchecked, ie both the same, Regards "Dave" <anonymous@discussions.microsoft.com> wrote in message news:328401c4a4e0$a7dacb20$a301280a@phx.gbl... > When we copy a date from an excel 2000 worksheet and paste > it in an excel XP worksheet the year will move back four > years. Any ideas? > > In article <328401...

how do i convert excel to csv format
I am trying to convert multi tabs excel file into csv. Can I get all the tabs in csv format in a same file? Tks If you combine all the data into one worksheet in a new workbook, then you could save that workbook as your CSV file. Ron de Bruin has some code at: http://www.rondebruin.nl/copy2.htm that might get you started. baxterr wrote: > > I am trying to convert multi tabs excel file into csv. Can I get all the tabs > in csv format in a same file? > > Tks -- Dave Peterson ...

Count of Weekdays Between Two Dates
Task: With a given start date and end date, print a list of Months showing a count of weekdays and weekend days in each. It seemed like such a simple request... but how to get started? If I already had a table of all dates between the start and end dates, I'd have it whipped. Any thoughts appreciated. -- croy Here are some ideas you can use for this. First, you need to know how may days are in the month. Then you can use the function below to return the number of work days in the month. The subtract the work days from the number of days, and it will return the week end days. ...

Please Help!!! credit/debit card processing
I know there is way to set up the credit/debit card processing without actually using ICVERIFY or PCCHARGE. I am guessing you would have to emulate something but, I am not really sure on how it is done. Any kind of input will be greatly appreciated. RMS does come with an integrated Credit Card processing solution. Before you can set up Store Operations to process credit and debit card transactions through Citibank, you must have an Internet connection. Your connection must support SSL (Secure Sockets Layer) and HTTPS: (Port 443). Processing through Citibank does not support direct...

Custom validation rule
Hi, I have been using this formula to round time values to the nearest 15 minute increment: =ROUND(E10*96,0)/96 This works fine for cells where the value is auto-calculated. However, I have one cell where a value has to be manually entered are strictly 15 minute increments? What I thought I might do is create a Data Validation rule that checks to see if the value * 96 is an integer. If it isn't then it fails. How would I do this? I know it can be done, I am just having a bit of a block. TIA, Jarryd =INT(F13*96)/(F13*96)=1 "Simples"! Cheer...

time limit Database
hi all how can i add a time usage validation in my data base? thanks On Wed, 6 Feb 2008 03:49:01 -0800, Osyris <Osyris@discussions.microsoft.com> wrote: >hi all > >how can i add a time usage validation in my data base? >thanks Could you explain what you mean by "time usage validation"? John W. Vinson [MVP] for example.. i will give my database to my friend as a trial, to work and if he try to open the database after 10 or 20 days to see a pop up windows said " the trial version has expired" any ideas?? "John W. Vinson"...

first value of the month from a list of dates
Hello gurus, I've read through a list of message thread, and you guys are wonderfu ! . Experts !. Anyway, I hope somebody can help me with this problem. I have a list of share prices together with the dates. (about 20 to 2 dates in a month). How do i extract out the first day of every month from a list o database of 3 years (day by day) ? I think hard, but no avail. Help... -- Message posted from http://www.ExcelForum.com Hi one way: - add a helper colum adjacent to your data list (lets say column C) - in C1 enter the formula: =DAY(A1) ->if column A stores your dates - copy do...

help with separate email accounts
hi- i have multiple email accounts with roadrunner- i've set up the accounts using the wizard On my Microsoft Outlook 200 sr-1 and entered all the pertinent info. however i don't know how to manage the accounts from the tool bar--in other words...i don't know how to check each and every email separtly. in my old Outlook Express 6.0., i would normally go to File-->Switch Identites---> and then i would select which email i wanted to go to--to write or read my Inbox....with my upgrade to Microsoft Outlook 2000 SR-1---i don't know how to do the same. I'm stuck ...

Help! error while installing CRM 3.0
Hi, During the installation of the CRM SBE 3.0 on SBS server 2003 premium, performing binary install of Microsoft CRM server components, I got en error, CRM 3.0 server setup failed, here is the last part of the crm30svrsetup.log. .... Installing MSCRM prepopulated data 08:49:50| Info| Creating indices in MSCRM database 08:49:51| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Data.SqlClient.SqlException: The Microsoft Search service cannot be administered under the present user account at System.Data.SqlClien...

Seaside Helps Shutdown the MSBlaster Worm!
Seaside Helps Shutdown the MSBlaster Worm! Internet Worm Shuts Out Email Users On Monday, August 11th, a new worm was discovered infecting machines across the Internet. The worm is spreading rapidly by exploiting a Microsoft Windows DCOM RPC vulnerability. The worm spreads itself by attacking port 135, the port used by Outlook Exchange clients to connect to Microsoft Exchange servers. In an effort to stop the spread of the worm, many organizations, including numerous ASPs and ISPs, have shutdown Internet access to port 135. Unfortunately, this also shuts down Outlook clients using MAPI t...

Project Accounting and Time & Expense connecting with MS Project
We need an easy way to connect hours reported for project inside Business portal to also be connnected with MS Project to better manage overall projects. MS Project should be able to look at the GP database of Time reports. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message ...

how can i make furmulas for converting gram to microliter,thanks
please help me to find how i can convert gram to microliter What kind of Liquid are we dealing with !? Micky "aedrion" wrote: > please help me to find how i can convert gram to microliter Hi aedrion, There is no direct conversion - grams are units of mass (weight); microlitres are units of volume. If, of course, you know how many microlitres of a substance weighs 1 gram, then you can do the conversion for that substance. -- Cheers macropod [Microsoft MVP - Word] "aedrion" <aedrion@discussions.microsoft.com> wrote in message news:CDE712...

Date prompt appearing twice due to chart in report
Hi All, I am creating a report based on a query. I have a prompt to ask for the Beginning Date and the Ending Date. When I run the query I get the correct results. I also get the correct results when I run the report. However, I have just added a pie chart in my report footer, and because of this, I am always forced to enter the beginning and ending dates twice when running the report. What do I need to do to make the prompt only appear 1 time for each date? Thank you! decklun The prompt appears each time Access needs to run the query. Once for the report and a second time for ...

Help with Transpose problem
I trade in the stock market & use excel to convert a list of ticker symbols to a csv file to import to a charting program. I start with a vertical list in excel, I copy then paste special & tell it to transpose. It seems if the list is too long I get an error message: "The information cannot be pasted because the copy area and the paste areas are not the same size and shape. Try one of the following: Click a single cell then paste. Select a rectangle that's the same size and shape, and then paste" I assume what is happening is that I don't have enough columns across...

POP connector
Hi, I'm a total newbee to Exchange! I've just installed SBS 2003 and configured the POP3 Connector in Exchange to retrieve mail messages from the POP mailbox to my Exchange mailbox. Everything works fine besides that all mail messages get the date from synhronization in stead of the actual mail message date. Can this be configured so the date of a mail message in the Exchange mailbox is the same as the actual date of the mail message in the POP mailbox? Thx, Dennieku On Fri, 22 Jul 2005 08:13:50 +0200, "Dennieku" <dennieku@hotmail.com> hired a team of monkey...

How do I enter the date last modified in the footer of an Excel d.
How do I enter the date last modified in the footer of an Excel document? I want to be able to distribute a monthly or weekly report in Excel to users and have it automatically update the date last modified in the footer each time I update the report. You need to do that with a Before_Print event macro. If you search the newsgroups from Google you will find lots of examples. On Fri, 4 Feb 2005 09:33:04 -0800, "Last modified date in footer" <Last modified date in footer@discussions.microsoft.com> wrote: >How do I enter the date last modified in the footer of an Excel doc...

Help! Money 2k4 wont work!
Everytime I try to run M2004, I get an error message, including restart M2004. But it will not load. Tried reinstalling etc... Nothing seems to work. Can anyone help me to get money 2004 up and running again? THANKS! And that error message would be............ "Malcolm" <anonymous@discussions.microsoft.com> wrote in message news:0e6201c3c685$018d66e0$a501280a@phx.gbl... > Everytime I try to run M2004, I get an error message, > including restart M2004. But it will not load. Tried > reinstalling etc... Nothing seems to work. Can anyone > help me to get money 200...

Liaison Messenger vs. Forms Printer opinions needed
Hey everybody - Can somebody give me some review of which product they prefer? We need to send out invoices from GP with excel attachment and both of these products look like they can do the job. Any suggestions? Hi-- I don't have as strong opinion, but i have worked with Liaison Messenger mostly. It is easy to use and pretty functional and the support is fabulous. My only issue (or thing that you need to be aware of) is how it is structured. The machine the runs the server application has to be logged in (it can be logged in and locked) with the server app and outlook launched for ...

set or reset the print range for several sheets at a time?
how do you set or reset the print range for several sheets at a time? Manually I don't think it's possible to set more than one sheet's print area at a time. Of course a macro can make this a lot quicker. -- Jim Rech Excel MVP "Mestrella31" <mestrella@mxn.com> wrote in message news:BF8D4BDF-1787-4633-B5D0-AA6F2E4732AF@microsoft.com... | how do you set or reset the print range for several sheets at a time? Unfortunately setting the print area on grouped sheets is not an option without using VBA. Code from Bob Flanagan for setting same print area on groupe...

Reading one Record at a time till i reach EOF in VC++ using ODBC
Hi, i am working on Vc++6.0. I hve a req where in i need to read one record at a time till i reach EOF from the database.Can anyone tell me how to do this Very urgent Thanks Use CDatabase and CRecordSet classes. // Embed a CDatabase object // in your document class CDatabase m_dbCust; // Connect the object to a // read-only data source where // the ODBC connection dialog box // will always remain hidden m_dbCust.OpenEx( _T( "DSN=MYDATASOURCE;UID=JOES" ), CDatabase::openReadOnly | CDatabase::noOdbcDialog ); CRecordSet rs;rs.m_pDatabase=&m_db...

Help Freezing Multiple Rows
In earlier versions, you clicked below the rows you wanted frozen in place or to the right of the rows you wanted frozen. Now I see only how to freeze row 1. I have a main title in row 1, row 2 is blank and row 3 has column headings, so I want row 3 to stay frozen in place. I am having trouble accomplishing this and need help, please. Thank you. -- Virgo click on cell A4 then on the menu bar Window/Freeze Panes "Virgo" wrote: > In earlier versions, you clicked below the rows you wanted frozen in place or > to the right of the rows you wanted frozen....

Date function help
I have order received date. I will start the order on the next comin THURDAY always. My delivery date is 9 days from the start. eg. Received date = 5/7/2004. Start Date is = 8/7/2004. Delivery dat will be 17/7/2004. Can somebody help with a formula to calculate delivery date. By the wa we are into furniture manufacturing. Thanks in advance, Itt -- Message posted from http://www.ExcelForum.com > ... My delivery date is 9 days from the start. > .. a formula to calculate delivery date. If col B contains the start dates, from row2 down just put in say, C2: =B2+9 (think Excel will fo...

Do I need to run eseutil?
Was reviewing 1221 events in application log and spotted a jump in available space from 500MB to about 3000MB in one night. Recently ran eseutil against the database. Does this jump in free space mean I may need to run it again? No, it just means you wasted your time the last time you ran it. I see no reason to run an offline defrag unless you are out of disk space and have no other options, are running against the 16GB standard limit or have moved a "substantial" number of mailboxes and created a large amount of white space in the database. On Mon, 18 Oct 2004 15:55:08 -0700, ...