How do I sort data by date excluding time

I would like to sort a database by date and transaction type. The problem is 
the data sorts by time, even though the field is formatted to show date only, 
with the transaction type not sorting within the date because it shows up in 
the time order. How do I get rid of the time?
0
Utf
1/29/2010 6:54:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
822 Views

Similar Articles

[PageSpeed] 13

If date/time column is A then you have to use an empty helper column filled 
with formula =INT(A2) and sort by it.

-- 
Regards!
Stefi



„markd” ezt írta:

> I would like to sort a database by date and transaction type. The problem is 
> the data sorts by time, even though the field is formatted to show date only, 
> with the transaction type not sorting within the date because it shows up in 
> the time order. How do I get rid of the time?
0
Utf
1/29/2010 7:28:04 AM
Reply:

Similar Artilces:

Displaying hidden data on a chart
I have a number of data series in a table on sheet 1, and this data is plotted as an x-y scatter in a chart on sheet 2. I want to hide a column in the table, but when i do this the related data series disappears from the chart. Is there a way to hide a column whilst still having it plotted on the chart? At the moment I have just set the column width to 1 pixel, which is a bit messy. Thanks Hi, Yes, select the chart and then via the menu Toosl > Options > Chart. unchecked the Plot visible cells only. Cheers Andy Nick Terry wrote: > I have a number of data series in a tabl...

Secure deletion of data from Exchange server networks.
I would like to completely eradicate messages from my exchange server network. When I say eradicate i don't mean detonating my servers and melting them down for scrap or running 'Boot & Nuke' disks but i want at the very least to overwrite the clusters on which the data was stored so as to prevent recovery of the data without examination of the disk platters. This as far as I know, would require: Deleting messages from the server message store. Deleting entries from the server logs. Deleting messages from the client .ost file. Deleting entries from client logs. Running a...

Payables Invoice Due Date
I have a bunch of payable invoices that have dues date in the far away future. instead of the user typing 2003, they have put 2030. The voucher has been posted, but becuase the date is in the future, it did not apply and it still shows up on my aged trial balance report even though we have already payed it. I have gone into the tables and changed the due date back to 2003, but this still did not fix the problem. Any ideas? Thanks! Darren, You're saying that you have invoices that are fully applied, so when you look at them in Vendor Inquiry they have an 'Origin' of H...

sorting non contiguous ranges
I have a worksheet with student demographic data. Column A has name, Col B has address, Col C has instructor, Col D has instrument, etc. There are command buttons at the top for sorting the data, 1 for the student name (ColA), another for sorting by the instrument etc. The student names are Paste Linked to a second worksheet. Problem: When the sort is triggered from the first worksheet, the names on the second worksheet change according to the paste link. I would all the information on the second sheet to move with the names if any sorting is triggered on the first worksheet. Now when a sort...

RUN-TIME ERROR IN SIMPLE SORT MACRO
I have a simple macro that is supposed to call a named range 'Catalogue' and sort it in a specified order. But I always get "Run-time error '1004': This operation requires the merged cells to be identically sized." The macro is: Sub SortNFAuthor() ' ' SortNFAuthor Macro ' Macro recorded 13/9/2004 by Lindsay D. Graham ' ' Keyboard Shortcut: Ctrl+Shift+S ' Application.Goto Reference:="Catalogue" Selection.Sort Key1:=Range("K3"), Order1:=xlAscending, Key2:=Range("A3") _ ...

Sorting 2 or more rows
Is it possible to sort and keep two or more rows together? I have a customer list that has the customer name in row #1 then in ro #2 and #3 are that customers branches, along with there total loans. want to sort descendingly on the total customer loans, but keep th customer and branches together Example: #1 First Union Bank #2 First Union Bank North 5 loans #3 First Union Bank South 10 loans #4 Total 15 loans #5 American Bank #6 American Bank South 4 loans #7 American Bank North 16 loans #8 Total 20 loans In the end...

Date Picker 01-26-10
I have date picker on a template but when I go to choose the date the calendar month is still December. Is they a way for the date picker to be on the correct month? When you open the template itself (as oppose to creating a new document based on that template), does a December date show as a placeholder? If it does, then click the Developer tab and click the Design Mode tool so that you're in design mode (this is not essential, but it makes it easier to avoid deleting the whole content control rather than just the placeholder date). Delete the placeholder date from betw...

Code to select data
Hi - I am using v2007 and want to know code for selecting from row 2 to the end of populated data in a spreadsheet. The length of the data will vary everytime. Any help gratefully nreceived. Thanks. Anita Assuming you mean you want to select entire rows, give this a try... Dim LastRow As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row If LastRow > 1 Then Range("2:" & LastRow).Select -- Rick (MVP - Excel) "Anita" <Anita@discussions.m...

Absence/Time Tracking
We are currently using GP 9 Human Resources, to upgrade to 10 shortly, and are looking for a 3rd party software to support our Absence & Time Tracking. We need this front-end application to be in both English and French and to integrate with GP 9 & 10. Is anyone out there using a bilingual application that will integrate with GP? -- Linda ...

Data in columns
Good afternoon, In Access 2007, I have a table that looks like this: EquipID Text (5) ChackDate Date UserID Text(2) Check1 Y/N Check2 Y/N Check3 Y/N Obs Text (255) .... I need a report that looks like this: 01/01/2007 Equipment A1 A2 A3 User MF FO MF Bolts secured Y N Y Instruction Manual Y Y N Packaging ...

Dates in subform
I need to make a Form where the user type: SampleID: 1234 StartDate: 01-01-2010 EndDate: 01-01-2015 CheckEvery: 3 (There will be more fields but these are the most important) On this form there should be a subForm because in the subForm it will show all the due dates: 0 3 6 9 12 15 01-01-2010 01-03-2010 01-06-2010 01-09-2010 01-12-2010 01-03- 2012 ...and it continues until it reaches the last date 01-01-2015. In the “checkEvery” textbox the user should be able to write a ...

Averaging Time
Hello: I have data that looks like the following: 3:09:15 22:45:07 17:39:10 17:39:10 18:52:47 8:39:35 I would like to learn a formula that will round the time to the top and bottom of the hour using the 15 and 45 as the break point. For example 3:09:15 to 3:00. 17:39:10 to 17:30. Thanks for the hedlp. Jim =MROUND(A18,TIME(,30,)) and format the cell accordingly. If you have problems with the MROUND function, look it up in Excel help. -- David Biddulph "Jim" <Jim@discussions.microsoft.com> wrote in message news:E5FACE1B-C1BF-412B-8CB6-7AE1C24F6...

Run time error -2147217887 (80040e21)
Hi I am trying to add a record to a table My code is as follows: Dim rs As New ADODB.Recordset rs.Open "SELECT EmpJobID, JobID, EmpID FROM tblEmpJob", CurrentProject.Connection, adOpenKeyset, adLockPessimistic With rs .AddNew !JobID = lngJobID !EmpID = intEmpToID .Update End With rs.Close Set rs = Nothing It is failing on the update. The error is Run Time error -2147217887 (80040e21) The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Ch...

Timing of payroll year end tax update installation
I am trying ot find out if the 2007 year end tax update for GP v9 can be applied prior to running the final payroll for the year. If this is just a form update can it be done early to avoid so much stress at the end of the year? Has anyone tried this? (I do not mean the new tax table update for 2008) -- Diane Burbank Dynamics GP Consultant Yes, it can be applied at any time. However, it is not just a form update. It includes service pack 3. The installation will require all users be out of GP during the procedure. All workstations must be updated. Make backups of the databases and the...

Why is my Excel spreadsheet's sorting not being remembered?
I have a workbook with multiple spreadsheets. As I change/add data, I resort these sheets. One of my spreadsheets defaults the sorting criteria I used in the previous sort and remembers that I use headers. The other spreadsheet used to do the same, but now does not 'remember/default' any of my previous sort information or the fact that the spreadsheet contains headers. Is there a setting that I can change so that this sorting information is remembered? Thanks much, Lisa ...

Extra Spaces with copied data
I recently had to copy a large amount of data from my company's website. When I copied it over to Excel all cells had trailing spaces, 5 spaces to be exact. What I need to know is how do I sweep through and get rid of all those extra spaces. They are interferring with calculations and sorting of numbers. Thanks for all your help. -- shane24 ------------------------------------------------------------------------ shane24's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4770 View this thread: http://www.excelforum.com/showthread.php?threadid=490089 Tr...

Bar data not displaying
I am trying to create a bar chart that refers to a set of cells which are calculating the remaining time after subtracting the differences between beginning and end times from a static time value (1:15) using the following formula: =IF(AND(COUNT(D9:DL9)<>0,COUNT(D9:DL9)=COUNT(D8:DL8)),$B$2-(SUM(D9:DL9)-SUM(D8:DL8)),B2) The B2 cell refers to the static time of 1:15 that is formated as h:mm. If there is no start and end time values in the range and therefore the cell is assigned a IF-FALSE value of 1:15, when the bar chart is created there is no bar value displayed in the chart. If...

Exclude Records via Join?
Hi All, I have a make table Query that pulls records which include invoice numbers (table name is PromoTableAgentIncentive). I have a query (Promo_AgentIncentive_Exclude) that contains invoice numbers that are in the PromoTableAgentIncentive but should be excluded from the final product. How do I join these fields so that it excludes them? I have a feeling that it can only be done in SQL. I do not know SQL very well (only from copying and pasting and then modifying for current query) but am trying to learn. I've copied the SQL for the query below but the join is not working. ...

Rules and Alerts by time frame
Outlook 2007 Is there a way to setup a rule to only processing during a certain time frame (i.e. 08:00 - 17:00)? "brent" <brent@discussions.microsoft.com> wrote in message news:02A73DA2-562E-49B8-8DE7-E5E0643D3998@microsoft.com... > Outlook 2007 > Is there a way to setup a rule to only processing during a certain time > frame (i.e. 08:00 - 17:00)? No. -- Brian Tillman [MVP-Outlook] If it is on the local machine it will only process rules while outlook is running. brent wrote: > Outlook 2007 > Is there a way to setup a rule to only ...

How to get data from Yahoo calender ?
Hello all, I am developing an application in VC++/MFC to fetch the data from the Yahoo calender. I searched the Yahoo developers site but i am not getting any clue that from where to start. Can anybody tell me that how to start? Means suppose i would have user's Yahoo ID and Pswrd then which URL should i call so that i will get the Yahoo Calender data(Events in it etc.) Thx in Advance... Rohit ...

Microsoft CRM Data Migration Framework could not initialize
Hi, I've got CRM 3.0 installed with roll-ups. I've installed the DMF so I can import a large number of records but each time I try to start it up I get the above error. The detailed information says :- * The Microsoft CRM Server is not responding. A WhoAmI() call did not succeed. * SOAP Server Application Faulted Nothing is showing in the event logs and I've checked that the IIS service is working correctly and the databases is also contactable. I'm not sure how to go about troubleshooting the problem because I can't see how I can trace what its doing. I've ...

How do I force a secondary horizontal axis to act as a date axis?
I have a chart with multiple data series, all using the same column of dates for the horizontal axis. I want to set some of the series to a secondary vertical and horizontal axis, so I can time-shift the data in these series on the graph. However, when I put these series on a secondary axis, the secondary horizontal axis does not provide the same options as the primary axis (which is derived from the exact same data). For the primary axis, under "Format Axis -> Axis Options" I am able to select a minimum, maximum, major unit, minor unit, a dropdown of days/months/years, ...

POS runs very slow at times
It randomly slows down in both POS and Manager for no apparent reason. ---------------- 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 pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=f664e37e-a73e-4a70-81e4-fcb66ed31204&dg=microsoft....

Usage data
I am interested in developing an application that monitors data usage. For example, I need to know how many rows that a user accesses from specific tables; maybe also, what columns they access. This data should probably be gathered at the data-access layer because it would be more certain and not make me rely on having to parse ad-hoc queries. Does SQL provide any sort of monitoring tool that can give this data? Suggestions? Thanks, Howard SQL 2008 has a lot of features and built in reports... also maybe performance dash board? (It's a seperate installation package) ...

Date Type Mismatch
I have a main form with a subform. The subform also contains a subform. On the main form is a text box that contains the following code in the data source of the PrintCount field: =(DCount("PrintID","tblPrint","FilmNumber =" & FilmID]) In the primary subform's Form_Current event, I refer to the value of this field on the mainform using this code: If Forms!AA_frmFilms!PrintCount > 1 Then When the forms originally load, the code executes and the PrintCount reference on the subform works as expected. If I then click on a button on this subform to...