sorting by date with day "ranges"

Sorry if I missed something really easy. But I have two columns one
with dates "Apr 12-15" and May 2-4", etc... and the other column with
locations - Omaha - st. louis, etc....

What I want to do is sort by date and take the day range in account.
Now I know how to sort under the data pull down if I dont have the day
"range" in the same cell and everything works fine with no numbers or
just a single day(like 5-apr-2004). But I don't want to make another
column for the end date if I dont have to. I thought I could do
somthing in the custom format option like mmm-dd - dd but that only
puts the same day twice. I would like to keep all the date info to one
cell. thanks for any advise.


-dj

btw - Desired output

Date                 Location
Jan 3-8            St. louis
Feb 21-27          omaha
Jun 15-20          D.C.

etc.....
0
1/14/2004 7:29:08 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
224 Views

Similar Articles

[PageSpeed] 50

You should make another column for end date, you will never be able to sort
that
without using a couple of help columns that will parse out the real dates
since what you have is pure text.
And since you would need help columns you wouldn't save anything. As a
ground rule, always put
numeric value in distinct cells..

-- 

Regards,

Peo Sjoblom


"DJ" <dr_jackson@yahoo.com> wrote in message
news:42b552b5.0401141129.4f43920f@posting.google.com...
> Sorry if I missed something really easy. But I have two columns one
> with dates "Apr 12-15" and May 2-4", etc... and the other column with
> locations - Omaha - st. louis, etc....
>
> What I want to do is sort by date and take the day range in account.
> Now I know how to sort under the data pull down if I dont have the day
> "range" in the same cell and everything works fine with no numbers or
> just a single day(like 5-apr-2004). But I don't want to make another
> column for the end date if I dont have to. I thought I could do
> somthing in the custom format option like mmm-dd - dd but that only
> puts the same day twice. I would like to keep all the date info to one
> cell. thanks for any advise.
>
>
> -dj
>
> btw - Desired output
>
> Date                 Location
> Jan 3-8            St. louis
> Feb 21-27          omaha
> Jun 15-20          D.C.
>
> etc.....


0
terre08 (1112)
1/14/2004 7:34:37 PM
"Peo Sjoblom" <terre08@mvp.org> wrote in message news:<Osmd2Vt2DHA.3416@tk2msftngp13.phx.gbl>...
> You should make another column for end date, you will never be able to sort
> that
> without using a couple of help columns that will parse out the real dates
> since what you have is pure text.
> And since you would need help columns you wouldn't save anything. As a
> ground rule, always put
> numeric value in distinct cells..
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> "DJ" <dr_jackson@yahoo.com> wrote in message
> news:42b552b5.0401141129.4f43920f@posting.google.com...
> > Sorry if I missed something really easy. But I have two columns one
> > with dates "Apr 12-15" and May 2-4", etc... and the other column with
> > locations - Omaha - st. louis, etc....
> >
> > What I want to do is sort by date and take the day range in account.
> > Now I know how to sort under the data pull down if I dont have the day
> > "range" in the same cell and everything works fine with no numbers or
> > just a single day(like 5-apr-2004). But I don't want to make another
> > column for the end date if I dont have to. I thought I could do
> > somthing in the custom format option like mmm-dd - dd but that only
> > puts the same day twice. I would like to keep all the date info to one
> > cell. thanks for any advise.
> >
> >
> > -dj
> >
> > btw - Desired output
> >
> > Date                 Location
> > Jan 3-8            St. louis
> > Feb 21-27          omaha
> > Jun 15-20          D.C.
> >
> > etc.....


Arrgh!!! OK I thought so. Peo, Thanks for the help.
0
1/15/2004 1:43:19 PM
Reply:

Similar Artilces:

DYNAMIC RANGES #2
I am looking to develop a macro that will allow me to select a range of cells dynamically. I have created a mapping template that maps user defined fields to native fields, but each file that I map has a different number of rows of data. Rather than simply selecting a range of cells statically, I am hoping that there is a macro out there that will allow me to catch all data in a column by dynamically assigning my range to include all rows of data up to the last row containing information and no more. To add to this issue, not all intermediate rows will contain data, but it is important tha...

max number of data ranges (bars) in a bar chart
I am trying to create a simple bar graph with 23 different data ranges, there is all the room in the world for 23 bars in a single graph, yet when I start selecting the data ranges (or labels for that fact) it gets to about 17 of them, and then erases everything I have selected and starts over. Ending up with a graph that only contains the last 4 or 5 data ranges????? I have also search every help and internet and see addressing this??? Hi, Is your data in non-contiguous ranges? If so this will create a reference that maybe too long. Either move the data to a contiguous range or crea...

Printing ranges #2
I have three different ranges ie A37:M50,A98:M111,A152:M160 and would like them to print on one page - is this possible? Thanks T Hi see: http://www.rondebruin.nl/print.htm#non-contiguous -- Regards Frank Kabel Frankfurt, Germany "Tim" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:12de801c4435f$8e0ff0e0$a401280a@phx.gbl... > I have three different ranges ie > A37:M50,A98:M111,A152:M160 and would like them to print > on one page - is this possible? > > Thanks T Take a look here: http://groups.google.com/groups?oi=djq&ic=1&s...

Trouble Using Named Ranges in a Chart
I am trying to use a named range as series data for a chart. My named range has a OFFSET function set up so the range dynamically adjusts when new data is added. The trouble is when I enter the name into the "values" box it get at "The formula you entered contains an error" box. Is there a trick to entering Names? Thanks Vern Jon Peltier has some notes at: http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/ChartSeriesFormula.html that might help. (He's got a couple of common errors to avoid at the bottom of the page.) Vern wrote: > > I am trying to u...

Print Ranges
Some of my spreadsheets are 200 lines deep and 30 columns wide. There might be three pages on each that I need to print and I have been setting the print area three times in each worksheet to print the required pages. When I used Lotus years ago I was able to set and name ranges which simplified the process of printing multiple, specific pages. If this can be done on Excel I would appreciate your help. Hi Phil, When you select the print area via File-->Print Area -->Set Print Area (as you have been doing), a named range is automatically created, named Print_Area. Go to Insert-->Nam...

This is a repeat question about ranges
For some time now I have been trying to find out how to: 1. copy a range named say, RangeA1J1 in say cell a2to J2. I have been given several answers and I am sure some of them are ok but I just simply can't get it to work. Thanks L. T., You have this same question in another group, .misc. No one reading this post will know that the answers you've been given are not what you want. You should follow up those answers, and folks will try to help. I've looked at the other post, and the answers, and I don't know what it is that you need. Also, you should give it a few hours,...

Macro (Print)
I want to develop Print Macros so as to automate selection and printing of multiple named ranges within the same sheet. I'm stymied... Question - How do I call a specific named range? Using the macro I developed (see below), only the 1st cell in the named range prints. Example=>> I have a Budget workbook with 5 sheets (2005-2009). Each sheet has multiples schedules (Income Statement with detail schedules supprting particular line items). I want to print all schedules at one time, thereby enhancing efficiency. The attributes of the ranges may change during developemnt of the w...

Using mathmatical functions on ranges that have #NA
I need to take the median of a column of data, but some of the cells in my intended range have the #NA or #Value error. When you try to use the functions with data cells that have error values, the result is an error value. I can't filter the rows of data because I need to have visibility to the information in other columns. median(a1:a5) = #NA A1 5 A2 6 A3 7 A4 8 A5 #NA Thanks I Mike, =Median(IF(ISNUMBER(A1:A5)=TRUE,A1:A5)) array entered (ctrl+shift+enter) Formula will appear in "{ }" HTH PC "mike" <mar@kurtasalmon.com> wrote in message news:008...

Pivot Table
This is my first post here...I hope that i've chosen the right category.... My problem is similiar to other people's when it comes to consolidating ranges with Pivot Table (sorry for my english...). I've searched the forum but I haven't found either solution or workaround for my problem. My data is divided into 2 sheets, because i need to use more than 255 columns (about 433). It looks like this: col1(ID) col2(Chain store category) col3(address) col4(date) col5-col255 (product's data). One product uses 9 columns like: space on a shelf, price, comments.... Bacause...

Defining ranges
In a reply to my thread "the terror of advancing cell numbers" a ver helpful reply was given: \"OR YOU COULD DEFINE A RANGE NAME THAT POINTS AT A1:Z100. SELECT YOUR RANGE AND TYPE: MYRNG (OR WHATEVER YOU WANT TO CALL IT) AND HIT ENTER THEN YOUR =VLOOKUP() BECOMES: =VLOOKUP(A1,MYRNG,2,FALSE I have tried this but could not quite do it. Selecting the range is n problem, of course, but where do I type "myrng" (or whatever I wish t call it)? Can someone help this poor newb with more precise direction for defining ranges? Thanks Davi -- Sivods -----------------------...

Embeded IF
Using Excel 3. I am trying to assign ABC codes to long list of products based on % of sales. A10 is part #, B10 is %. In C10, need formula to pick a code from following table. The table changes periodically. Code A=over 80% Code B=between 60 and 80% Code C=between 40...60% Code D = less than 40% -- Emily >The table changes periodically. Create a 2 column table like this... 0%...D 40%...C 60%...B 80%...A Assume the table is in the range J2:K5 Then...this formula entered in C10 and copied down: =LOOKUP(B10,J$2:K$5) -- Biff Microsoft Excel MVP ...

more than 2 secondary y axis with individual ranges of value
Hi All, I work with electric motors which have several different value ranges i would like on a single chart, namely; Speed, Power, Current and Efficiency, with thier own Y axis, and all plotted against Torque on the X axis. I need seperate Y axis as the ranges are all different, e.g. Speed = 0 - 4000rpm, Efficiency = 0-85%, Current = 0-20Amps, Power = 0-150Watts for example. At the moment i can only get 2 different Y axis values, I need 4! Can anyone help? Hi, You will need to construct you own additional Y axis. For more information have a look at http://peltiertech.com/Excel/C...

Problem with graph ranges
I actually have two problems related to graph ranges: 1) I have a 6-column text file, each column separated by tabs (\t) and each row separated by newlines (\n). However, each column does not have the same number of entries. When a column has less entries than the longest column of the 6, no data is present, just the "\t" to move on to the next column. I then select this entire 6-column text file and paste it into Excel. The cells line up perfectly, and the data looks correct. The trouble I am having is when I select an entire column by clicking the top of it to create an area graph...

Data Validation with named ranges
Hello, I have a wierd problem: in a planning module it should be possible to choose the correct product in a validated list. There is a matrix that shows only those articles that can be planned on a specific machines. Now when the possible values are mentioned in a fixed range [Art_CA0003 refers to =ArtikelPerMach!$C$2:$C$40], it works fine. However when I use a dynamic range [Art_CA0001 refers to = =OFFSET(ArtikelPerMach!$A $2;0;0;COUNTA(CA0001_);1)] it fails, allthough the range is shown as it should under insert>name>define. Hope my explanation makes sense. If requested I can send o...

Print Ranges & Grouping
I have a workbook with approx 20 tabs. Each Sheet has been formatted and prints correctly. However, when I group all the tabs and send them to the printer some sheets do not print right. It seems like Excel can't determine the setup has changed from Landscape to Portrait. Also some landscape sheets end up on two pages when they should be on one. The total workbook prints on approx 70 pages. In print Preview, I can see the same issue. Also, pageing forward I get one view, and then if I page backward I can get another view. Any help is appreciated. ...

ranges in excel???
Is there any way to put a range into excel, i.e. 1 to 10, and have it spit out data points at evenly spaced intervals between the two numbers. (in this case, I would put in 1-10 and excel would spit out 1,2,3,4,5,6,7,8,9,10. Thanks, mp Hi one way (if you put this in cell A1): Enter the following in B1 =IF(--LEFT(A1,FIND("-",A1)-1)+ROW(1:1)-1<=--MID(A1,FIND("-",A1)+1,10),- -LEFT(A1,FIND("-",A1)-1)+ROW(1:1)-1,"") and copy this down for as many rows as you need -- Regards Frank Kabel Frankfurt, Germany mp wrote: > Is there any way to put a r...

Letter Writing Assistant needs additional employee ranges
Currently, letter writing assistant only allows selecting of ranges by employee last name and employee ID. Larger employers would benefit from having additional sorts, for example by department, position or location. -- Patrick Anderson, SPHR HR and Payroll Consultant JAT Computer Consulting ---------------- 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-...

Multiple Print Ranges on Same Sheet
A few years ago, I used to be able to designate multiple print rangs on a single sheet and print same. Can anyone help refresh me as to how that is done? Thanks If you select your discontiguous range(s), you can then file|print area|set print area (be aware that this will print each area on a separate sheet of paper.) Dennis wrote: > > A few years ago, I used to be able to designate multiple > print rangs on a single sheet and print same. > > Can anyone help refresh me as to how that is done? > > Thanks -- Dave Peterson ec35720@msn.com Dennis, You can selec...

name ranges #2
Hi, Sheet2 has the formula: =VLOOKUP($B$3,Sheet1!$B$2:$D$15,2,FALSE) in cell c8 How can I use a range name (names-1 for example) that refers to: Sheet1 range B2:B15 in a way that i have a small arrow beside cell B3 that show all the names in the range (names-1)? Please give us more detail, this question is not clear. What does VLOOKUP have to do with the range names and what do you mean by the names in the range names-1. And what do you mean by a small arrow which shows the names? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Khalil Handal"...

How do I create ranges/groups in excel in a pivot table?
Using the data set below Product Cost A 1 B 2 C 2 D 6 E 7 How do I create a pivot table which groups the costs in costs from 0 - 5 and 6 - 10 and counts them The resulting pivot table would look something like this. Cost Count 0-5 3 6-10 2 Thanks in advance Ciar=E1n You could add another column and use that to categorize your values. Or you could create the pivottable by making the cost the row field and the product the data field (count of product). ...

Combine multiple tables with different date ranges
Hi everyone, I really need help please. I apologise in advance if my description is difficult to follow. I am very new to Access and have the following problem: Table 1 consists of: Machine ID (from another table) Downtime (calculated in queries from DateDiff expression based on Start Date & Time and End Date & Time) this table also has a few other fields - descriptive mainly This table is designed to track the amount of downtime for each machine and does so quite well, and even when there are multiple incidences of downtime in a day. It is for Daily entries as requ...

How to plot Date Ranges on Same Graph
I'm trying to take our personnel spreadsheet and chart employee arrival and departure data so that we can graphically see where the gaps are and whether or not we need to fill certain positions earlier than expected. When a date changes, the chart needs to automatically re-draw to correct itself. I thought, "oh, this will be easy- I'll simply chart the date ranges with everyone on their own line," and told my boss it would take 5 minutes. Well, that was a week ago! He's getting antsy, and I'm getting embarrassed. So basically, if person X is here from May throu...

Data Validation List
I am using Excel 2007. I have two named ranges: Customer_ID and Customer_Name. I currently have a data validation list with the source '=Customer_ID'. This works fine to look up customer numbers or I can change the source to 'Customer_Name' and look up by name. At some times it's easier to lookup by name, and others by number. Is it possible to have the drop down list display both? You would have to make a list of one column with the name and ID in each cell. I don't think this would be useable because one of them would not be sorted. Can you ...

Display ranges in a Pivot
Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help Just wanted to explain a little more. Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both g...

Procedure to remove all named ranges #3
Don I did use Chip Pearson's module to remove all VBA Code and module in addition to removing names. There are no modules present in the on worksheet that we are e-mailin -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26482 And you reviewed the notes at Debra Dalgleish's site???: http://www.contextures.com/xlfaqMac.html#NoMacros there have been some posts that say that Solver can leave a na...