|
|
|
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
Databases may sound very dreary but they are behind almost everything we do. We explain how to go about starting one of your own using Libre Office Base
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
Q.Why are some of the keys on my keyboard doing strange...
Q.Is my phone’s Bluetooth any use?
Q.Can I switch boot drives so that I can work on older...
Make a profit from your unused gadgets
Run Windows and Ubuntu on the same computer
Hide private folders
Save up to 40% on the price
Exclusive subscriber website
Our price won't be beaten
St Helena, a 'small British village' in the mid-Atlantic, is seeking support and funding for a broadband connection
|
|
|
|
|
Computeractive Excel (2010) Online tutorialPrice: £19.99 |
Computeractive Word (2010) Online TutorialPrice: £19.99 |
Computeractive Powerpoint (2010) Online TutorialPrice: £19.99 |
Angry BirdsPrice: £9.99 |
Back Issue CD-Rom 14 (2011)Price: £15.99 |
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