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.

Excel: Extracting Date Information

Discussion in 'Business Applications' started by dh134023, Jun 21, 2007.

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

    dh134023 Thread Starter

    Joined:
    Jun 21, 2007
    Messages:
    3
    I am currently logging in Excel customer complaints. Complaints are given a date stamp (in one cell in the form dd-mm-yyyy) at the time they are raised. I am now trying to perform data analysis on the complaints etc.

    Is there a way to extract the month and year information from each complaint into one cell (in the form mmm-yy) so I may log complaints per month say?

    Regards

    Dave
     
  2. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    All date and time information in Excel is stored as a single number.

    What you SEE in a cell is purely down to the formatting for that cell.

    There are a lot of macros available that will do what you have asked for, but 'one cell' will never work.
    Try looking at some of the Built in functions.

    COUNT if and some of the date ones would be a good start.
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to TSG Dave. :)

    There are numerous ways to do this, Dave B's points are valid to all of them. In the attached screenshot, the values in column C are 1st June -- 1st July -- etc., but they're formatted as mmm yy. The formulas in columns D are:

    =COUNTIF(A:A,">="&C2)-COUNTIF(A:A,">="&C3)

    in D2,

    =COUNTIF(A:A,">="&C3)-COUNTIF(A:A,">="&C4)

    in D3, etc.

    An ideal tool for analysing large qtys of data is the Pivot Table. Which version of Excel are you using?
     

    Attached Files:

  4. dh134023

    dh134023 Thread Starter

    Joined:
    Jun 21, 2007
    Messages:
    3
    Cheers guys.

    Using Excel 2003. I've just started using pivot tables, finding those quite useful.

    Found a little work around on the date extraction anyway but thanks for the help.

    This will keep me going.
     
  5. JackAndCoke

    JackAndCoke

    Joined:
    Apr 26, 2007
    Messages:
    152
    So I have the same problem. I have about 1800 recoreds that I deal with in a report on a business week cycle of Monday to Friday. They are entered in the MMDDYYYY hh:mm:ss format. I'm trying to do a trending report that graphs daily entries. When I use the pivot table is lists each individual minute. How can I get it to count quickly for each day? One of the problems is I run this report every monday, so I don't want to have to change the dates in the seven cells every week.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    In the table, rightclick one of the individual minutes. From the pop-up, select Group & Outline > Group. In the Grouping dialog, select Days (only), then click OK.
     
  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/586997

  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