Date &/or time sort - Page 3
Eliminate Pivot Table Annoyances
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 39

Thread: Date &/or time sort

  1. #21
    Board Regular
    Join Date
    Oct 2014
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

     
    Wayne - I have tried formatting simply as ddd (& DDD) - the full date data is not removed - I am most confused!

    as if I copy only the =value(right(B1,2)) data from the original Date column - the resultant copydown has offset all the days... e.g. Tues has become Sat...

    There are subtleties here that are beyond my rationalization!
    Last edited by Bebbspoke; Dec 6th, 2017 at 04:17 PM.

  2. #22
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    dont change the formula at all
    just change the date format
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

  3. #23
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    Sheet1

    B C D
    1 2017.11.29
    2
    3 29/11/2017
    4
    5 Wednesday
    6
    7 Thu Thu
    8 Thu Thu
    9 Thu Thu
    10 Thu Thu
    11 Thu Thu
    12 Thu Thu
    13 Fri Fri
    14 Fri Fri
    15 Fri Fri
    16 Fri Fri
    17 Fri Fri
    18 Sat Sat
    19 Sat Sat
    20 Sat Sat

    Spreadsheet Formulas
    Cell Formula
    B3 =RIGHT(B1,2)&"/"&MID(B1,7,2)&"/"&MID(B1,2,4)
    B5 =VALUE(RIGHT(B1,2)&"/"&MID(B1,7,2)&"/"&MID(B1,2,4))


    Excel tables to the web >> Excel Jeanie HTML 4
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

  4. #24
    New Member
    Join Date
    Feb 2014
    Location
    Arizona
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    FYI: deconcatenate = parse. You'll have better luck searching for that.

  5. #25
    Board Regular
    Join Date
    Oct 2014
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    Ty Tim - will try to remember that

  6. #26
    Board Regular
    Join Date
    Oct 2014
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    Hi Wayne - sure the formula =VALUE(RIGHT(B1,2)&"/"&MID(B1,7,2)&"/"&MID(B1,2,4)) gives me allows reversal of the Date sequence as was established...

    I can then format as ddd (only) to achieve each day in alpha form - but the day name is NOT separated from the full date information which is not displayed but exists in the cell as hidden code for the event... my aim is to be able to procure a list of events for each named day as contiguous for each day name - regardless at to (say) which week of the month or year the event occured. This is the real problem... thanks

  7. #27
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    create a new column and use =weekday()

    although formatting as ddd should sort all days together

    perhaps load the spreadsheet onto a share dropbox or onedrive
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

  8. #28
    Board Regular
    Join Date
    Oct 2014
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    Wayne - problem now solved!!! thank you again for your persistence & patience.

    Cheers, John

  9. #29
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

    you are welcome
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

  10. #30
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date &/or time sort

      
    perhaps load the spreadsheet onto a share dropbox or onedrive

    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
    Last edited by etaf; Dec 7th, 2017 at 01:43 PM.
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com