Solved: Data separation in Excel columns?

Discussion in 'Business Applications' started by PghDrake, Sep 14, 2009.

Not open for further replies.

Joined:
Sep 14, 2009
Messages:
8
Hi - I'm semi-proficient with excel, and I'm actually working on creating some dashboard reports at this time.

One problem I'm having is separating some of the data from a specific column. Let me give you an example:

9/1/2009 14:14 8/28/2009 7:33 7/17/2009 14:29 9/11/2009 12:28 9/11/2009 12:19 9/10/2009 18:53 9/11/2009 13:17 9/11/2009 13:27 9/10/2009 13:19 9/11/2009 13:08 9/11/2009 8:29 9/11/2009 13:01 9/11/2009 13:11 9/11/2009 2:00 7/22/2009 13:31 9/11/2009 13:04
This column has over 2600 lines, this is just a sample. What I need to do is break this column out by month, and show how many dates are from the last month, the last 2-3 months and the ones that are 3+ months in the past. Of course I want the sheet to show this info automatically, so it has to know the current date which I can do by placing the formula "=TODAY()".

So, what I'm asking for is what formula (and format) would I use to count how many cells in this column fall into each of date ranges I mentioned.

Also, I did manage to use the =DAYS360(A6,C9) (for example) where A6= "=Today()" and C9= "9/10/2009 13:19" above. This information is in cells next to the above data as follows:

-13 -16 -57 -3 -3 -4 -3 -3 -4 -3 -3 -3 -3 -3 -52 -3

So I figured out how to list how many days I have since the date in the first column, but not how to break them apart within the column.

Hopefully this is enough information to request some help, but if you need more info please let me know. I'll watch the thread closely.

Thank you!!!!

-Drake

2. The Villan

Joined:
Feb 20, 2006
Messages:
2,255
If its a question of splitting the dates and time into seperate cells, then use the example sheet I have uploaded.
I have copied your data into cells A1 to A10 and the text is flowing into the other cells but is only in the A cells. Its the same data in each cell but that doesn't matter as I am using it as an example of how to split the data into individual cells (Text to Columns)

Click on Cell A1 and select the cells down to A10
Click on Data, Text to Columns
You will get a dialogue box appear.

It should have the Fixed width option selected (if not make sure it is selected) and click on NEXT

Click on Next again

Click on Finish

Your data for all 10 cells should now be seperated into seperate cells going across the spreadsheet.

If you dont want the Time columns just delete them.

You may see ######## in the cells and you will need to widen the columns as they will probably not be wide enough to show all the data in a cell.

Anyway if thats what you were looking to do, thats fine. If not post back on here.

NOTE On your spreadsheet you will need to allow enough blank columns to the right to allow Excel to put the data into the cells to the right.

Attached Files:

• Text to Columns dates.xlsx
File size:
8.3 KB
Views:
46

Joined:
Sep 14, 2009
Messages:
8
Thank you for replying - but I obviously did not explain my self well and for that I apologize.

Attached is a better example of what I need.

What I'm looking for is the three formulas I would need for F2, F3 and F4. These numbers should be derived from the numbers in column C (which represent how many days from today the dates in column B are - for example, C2 shows that B2 is 14 days from A2 (Column A is today's date).

The actual numbers that the forumula should come up with in this example are: F2=9, F3=1, F4=0. I just can't figure out how to break down column C into those variables. For you guys it's probably simple but I'm going to break my desk banging my big head against it trying to figure it out without help.

Thanks!!!

-Drake

File size:
11.1 KB
Views:
46
4. The Villan

Joined:
Feb 20, 2006
Messages:
2,255
OK I have uploaded what I think you need.

I have changed your formula in column C to =DAYS360(B2,A2) i.e. B2 before A2

That way you get positive numbers and makes it easier to understand how to do the formulas in column F

F2 is =COUNTIF(\$C\$2:\$C\$11,"<=30")
F3 is =COUNTIF(\$C\$2:\$C\$11,"<=60")-COUNTIF(\$C\$2:\$C\$11,"<=30")
F4 is =COUNTIF(\$C\$2:\$C\$11,">60")

Hope that helps.

Attached Files:

• FormulaTest(1).xlsx
File size:
11.1 KB
Views:
50

Joined:
Sep 14, 2009
Messages:
8
Villan you rock! Thank you so much! That's exactly what I needed. I couldn't get that COUNTIF formula to work on my own, I must have just had the syntax all wrong. I've listed this thread as solved.

I'm sure I'll have more questions in the near future, and I'll also plan on checking back here to see if anyone is having problems that I can help them with - even though my knowledge is limited there's always things that I may have figured out that others don't know yet.

Thanks again, so very much!

-Drake

6. The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You are very welcome. Glad I could sort it for you.

As Seen On