Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2016
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts

    formatting dates

    Hi

    I have copied a years worth of data into exel and the date's format was originally set to general, I now want to change the format to date so I can use it for searches etc - however it just presents as 01/02/2017 then 01/03/2017 etc so always showing the first day for each month, then the second day etc where I want to group it into months its an absolute pain - any quick fixes ?

    Thanks in advance

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,728
    Thanks
    485
    Thanked 436 Times in 407 Posts
    I suppose that you could go into each spreadsheet where the data is, highlight the entire date column on each of these spreadsheets, and then change the format to date. Better, change it to "custom", with "mm/dd/yyyy" in the descriptor window.

    Sometimes, when I have tried to change the formatting from "general" to "date" or to "custom", I get a five digit number rather than a date, so be prepared to do undo a few times.

  3. #3
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,728
    Thanks
    485
    Thanked 436 Times in 407 Posts
    Another possibility: Text To Columns. This will work IF you have a two digit month and day, and either a two or four digit year, in EVERY cell, or IF there is always a slash between the month/day/year. If neither of these are true, then Text To Columns won't work for what you want.

    The idea is to put the day, the month, and the year each in their own column, making it easy to group data by month (or by month/year).

    * Create two blank columns to the right of the date column.
    * Highlight the entire date column.
    * Choose Data / Text to Columns.

    If there is always a slash between the month/day/year:
    * Choose Delimited
    * Select the slash as the delimiter
    * Click Finish

    If there is always a two digit day and month and always either a two or a four digit year:
    * Choose Fixed Width
    * Put the markers between the month, the day, and the year, as well as immediately after the year.
    * Choose Text format for the month, the day, and the year, and choose Do Not Import for the space after the year.
    * Click Finish.

    Now you can highlight the column headers row and turn on Filtering. Once you have done that, you can filter out everything except the month/year you want to look at.

    Or, just sort the whole thing by month and year.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,780
    Thanks
    403
    Thanked 1,551 Times in 1,405 Posts
    Robert,

    The first thing you need to do is determine the actual type of data you have as Dates:

    ExcelDateFmts.JPG

    As you can see in cols A-D what looks like a date is not always a date.

    If you get a value of 1 from the Type function you have a datevalue number. These can be easily sorted regardless of formatting as is shown in columns I & J.

    So my advice would be to determine what type of data you have and if it is not datevalue numbers convert it to such then sorting isn't a problem no matter what format you apply to the datevalues.

    HTH
    Last edited by RetiredGeek; 2017-04-20 at 11:19.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Robert Street (2017-04-21)

  6. #5
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,728
    Thanks
    485
    Thanked 436 Times in 407 Posts
    I would think that Text format is your safest format, if you want to guarantee that what you see displayed is what is actually stored in the cell.

    Extending that thought, if you want to be able to do Text to Columns like I have described above, you need to have the date in Text format.

  7. The Following User Says Thank You to mrjimphelps For This Useful Post:

    Robert Street (2017-04-21)

  8. #6
    New Lounger
    Join Date
    Jun 2016
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi

    Thanks for your suggestions

    In the end, what I did was insert a column named period - next to my non date and because it was formatted to 21/04/2017 in the UK format - I ended up doing a contains filter on */04/* to pick up all the April items and so on - I was then able to use the period in a pivot table.

  9. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,728
    Thanks
    485
    Thanked 436 Times in 407 Posts
    Pivot Table - that's one feature of Excel which sounds very useful, but which I have no clue about. One day I'm going to learn what Pivot Tables are all about.

  10. #8
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    729
    Thanks
    199
    Thanked 89 Times in 76 Posts
    Quote Originally Posted by Robert Street View Post
    I was then able to use the period in a pivot table.
    Robert, a pivot table will automatically group times and dates by anything from Seconds to Years, without you having to do anything except select which of those you want in the Group function—r-click on one of the dates in the pivot.

    I mostly use Month, Qtr and Year for my stuff. It's a neat function, it creates a separate field for each of those so eg I can have 2015 showing as only a Year, 2016 showing by Qtr, and 2017 showing by Month.

    You won't need your extra Period column, or any filters, to have April show as a pivot column. You can hide other date periods if you want your report to focus on just one or two months or quarters.

    Quote Originally Posted by RetiredGeek View Post
    As you can see in cols A-D what looks like a date is not always a date.

    If you get a value of 1 from the Type function you have a datevalue number.
    Alternative—I enter a formula in a blank column =cell+10, fill down, and sort. Quickly shows which are 'real' dates and which aren't.
    Lugh.
    ~
    Dell Alienware Aurora R6 (new 2017)
    Windows 10 Home x64 1703; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    256G SSD, 1TB HD

Posting Permissions

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