|
|
|
Unusual working hours affects spreadsheet data
Q I need help in sorting data in an Excel spreadsheet where the working day starts at 14:00 and ends at 06:00 the next day.
When I try to sort the data in this order, times between 14:00 and 23:59 appear in the correct sequence in the column but times after midnight 00:00 and 06:00 appear at the top the column and not at the end of the column where I want them to appear at the end of the day. I have to do a manual cut and paste to get around my ignorance.
Neil Tarry
A The simplest answer to this question is to add an extra column to the spreadsheet that includes the date.
Excel can sort by more than one column, so you can sort by the date first and then by the time. We’ll assume that the date is in column B and the time is in Column C. Select the range that you want to sort. Click on the Data menu and then on Sort. Click on Header row if you included the title row in the selection.
Click on the top dropdown menu and either select Column B or Date depending on whether you have a title row. Select Time or Column C in the dropdown menu below and make sure that Ascending is selected by both options. Click OK to sort.
Baffled by jargon? See our free online jargon buster
Article tags
Related articles
Software problems may be the cause of a computer running more slowly than it should, but there are some free tools you can try to improve things
It’s easy to hit the wrong key when using a PC and throw yourself into a panic. There's no need to worry - we have easy fixes for 30 common everyday errors
Microsoft Office and Open Office word processing and spreadsheet programs have simple ways of sorting lists of data in tables and spreadsheets
Q.How do I store musician and other information about...
Q.Why can't my browser find the website address I typed...
Q.All updates have been downloaded, so why won't Windows...
Be a money saving expert
Return of the Mac computer
The benefits of owning a smartphone
Choose Gold, Silver or Bronze membership
Save up to 40% on the cover price of each issue
Get access to the subscriber club exclusive website
Our price won't be beaten by any shop or website
Ticket-reselling company advises users not to click on links in emails from the company, allegedly offering an Adobe PDF, that is sent by cyber criminals
Grahics Interchange Format. A type of image file often used on the web, but now largely superseded by...
|
|
|
|
|
Nikon Coolpix S570 BlackPrice: £66.99 |
Computeractive Ultimate Guide - Storage, Sharing & BackupPrice: £5.99 |
Back Issue CD-Rom 13 (2010)Price: £9.99 |
Hallmark Card Studio DeluxePrice: £15.31 |
Marine AquariumPrice: £15.41 |
Excel Times
When I've had this problem in the past I usually enter the time in the early hours with 24 added to it. For example instead of 06:00 I'd enter 30:00. If you format the cells as Custom hh:mm then it displays it as 06:00 but when sorted it comes towards the end of the sorted list. Try doing a simple test using, say 06:00, 30:00, 18:00 & 12:00 in a column and sort it to see if it works.
Posted by Irbydatious, 20 Feb 2010