1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Data separation in Excel columns?

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

Thread Status:
Not open for further replies.
Advertisement
  1. PghDrake

    PghDrake Thread Starter

    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

    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:

  3. PghDrake

    PghDrake Thread Starter

    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
     

    Attached Files:

  4. The Villan

    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:

  5. PghDrake

    PghDrake Thread Starter

    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

    The Villan

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

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/860839

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice