Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Issue with Filtering Datewise.

  1. #1
    Neophyte shashikanth's Avatar
    Join Date
    May 2013
    Location
    Bangalore, India
    Posts
    4
    Articles
    0

    Issue with Filtering Datewise.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    convert dates to month.xlsx

    Data is displayed with date figures on month and month figures by date. Attached is the data.

    Please let me know how we can make excel treat the dates as dates and month info as month.

    Shashikanth
    +91 9620475594
    India

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    I am not understanding what you mean?

    Can you give examples of what you expect?


  3. #3
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    The entries are not actually entered as dates. I suspect they are exported from another program.
    Hope that helps

    Roy

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Try

    =DATE(YEAR(A2),MONTH(A2),DAY(A2)) in B2 & copy down. Format as mmmm
    Hope that helps

    Roy

  5. #5
    Neophyte shashikanth's Avatar
    Join Date
    May 2013
    Location
    Bangalore, India
    Posts
    4
    Articles
    0
    =DATE(YEAR(A2),MONTH(A2),DAY(A2))

    This didn't solve my problem. The data was extracted from our Antivirus Console. Information of all machines which communicated to Antivirus Server. But the issue is: The date(s) turned to Months and Month(s) turned to Dates. We don't want the time. We can delete/remove the time. Only the date field should be corrected. Example from the sheet I attached: The cell A2 has the value -
    1/3/2013 11:11 it is not January 3rd. it is March 1st. Like that all dates in A column are in reverse. If you notice, when you add a filter at A1, after December all other entries are not falling under filter correctly. Because it is 13th February 2013. Please help me with some formula to convert the existing Month as date and Date as month. Hope this description help you to understand my problem here.

  6. #6
    Neophyte shashikanth's Avatar
    Join Date
    May 2013
    Location
    Bangalore, India
    Posts
    4
    Articles
    0

    Question

    convert dates to month.xlsx

    Thank you all for responding. The data was extracted from our Antivirus Console. Information of all machines which communicated to Antivirus Server. But the issue is: The date(s) turned to Months and Month(s) turned to Dates. We don't want the time. We can delete/remove/ignore the time. Only the date field should be corrected. Example from the sheet I attached: The cell A2 has the value - 1/3/2013 11:11 it is not January 3rd. it is March 1st. Like that all dates in A column are in reverse. If you notice, when you add a filter at A1, after December all other entries are not falling under filter correctly. Because it is 13th February 2013. Please help me with some formula to convert the existing Month as date and Date as month. Hope this description help you to understand my problem here.

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Ok try this.

    Select Column A, then go to Home|Find&Replace|Replace (or CTRL+H)

    In the Find What field enter a space followed by and asterisk: " *" (without quotes)

    Leave the Replace with field blank... then click Replace All.

    Now, format column A as Date, picking the first option *3/14/2001

    Now, go to Data|Text to Columns. Click Next, then click Next again. Select Date from the column data format area, then in adjacent drop down, select DMY. Click Finish.

    Hopefully that did the job


  8. #8
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    That formula worked on your dummy spreadsheet
    Hope that helps

    Roy

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Roy, that formula doesn't work for me either, perhaps because we are on different regional settings... The OP sees the first entry as January 3rd, 2013, but in fact, he wants it to be March 1st, 2013.


  10. #10
    Neophyte shashikanth's Avatar
    Join Date
    May 2013
    Location
    Bangalore, India
    Posts
    4
    Articles
    0
    Hi There,

    Thank you very much for the solution. It worked. You made my day pleasant

    Cheers!!!
    Shashikanth Mantri

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •