+ Reply to Thread
Results 1 to 10 of 10

Sort dates and hours chronological

  1. #1
    Registered User
    Join Date
    03-21-2019
    Location
    Bucharest
    MS-Off Ver
    2019
    Posts
    8

    Sort dates and hours chronological

    Hello guys,

    Please help me to order chronological the dates from the document attached.
    I tryied Format Cells -> Number -> Custom -> and enterd more options like dd/mm/yyyy hh:mm ; mm/dd/yyyy hh:mm etc with no result
    The purpose is to display after 1/10/2018 23:56 ( line 359) -> 2/10/2018 00:00(2 october 2018), not 1/11/2018 00:00 ( 1 november 2018) like in the doc.
    I need to do this for all 6 months from the document.

    Thank you and have a good day!

  2. #2
    Registered User
    Join Date
    03-21-2019
    Location
    Bucharest
    MS-Off Ver
    2019
    Posts
    8

    Re: Sort dates and hours chronological

    Here is the attach, sorry for spam
    Attached Files Attached Files

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Sort dates and hours chronological

    1/10/18 it is not 1 Oct, it is 10 Jan that why next is 1/11 as 11 Jan.
    Check your Windows regional date/time settings.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Registered User
    Join Date
    03-21-2019
    Location
    Bucharest
    MS-Off Ver
    2019
    Posts
    8

    Re: Sort dates and hours chronological

    And what date is 14/03/2019? I don t have problems with windows time.

    How do you explain the following lines?

    Line 28861 14/03/2019 23:56

    Line 28862 14/10/2018 00:00

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Sort dates and hours chronological

    Quote Originally Posted by Flavius24 View Post
    And what date is 14/03/2019? I don t have problems with windows time.

    How do you explain the following lines?

    Line 28861 14/03/2019 23:56

    Line 28862 14/10/2018 00:00
    You have to agree that it can't be 14th month, right? So obviously it is 14 March and 14 Oct.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,627

    Re: Sort dates and hours chronological

    If the default date format is mm/dd/yyyy try both formulas.
    Data in A2 14-02-2018 12:36
    In B2

    =DATE(MID(A2,7,4),MID(A2,4,2),MID(A2,1,2))+TIME(MID(A2,12,2),MID(A2,15,2),0)

    =DATE(MID(A2,7,4),MID(A2,4,2),MID(A2,1,2))+TIME(MID(A2,13,2),MID(A2,16,2),0)
    Last edited by kvsrinivasamurthy; 05-08-2019 at 12:17 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    03-21-2019
    Location
    Bucharest
    MS-Off Ver
    2019
    Posts
    8

    Re: Sort dates and hours chronological

    Unfortunately, no result.
    In column A I have date and time, and in B,C,D,E other variables.
    I tried in the document attached where only column A is populated, but it doesn`t work.

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,313

    Re: Sort dates and hours chronological

    As 'KOKOSEK' as already pointed out it is most likely your regional settings causing your problem.
    The Romanian 'short date separator' is a '.' (point) NOT '/' .
    You need to convert or replace the date element.
    Where was the original data sourced from ????

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,627

    Re: Sort dates and hours chronological

    This problem is due to date format. Default setting is mm/dd/yyyy. Data in Column A is in dd/mm/yyyy.
    Copy the file and run this macro. It will set right the dates. Dates are already in chronological order.
    Code for macro
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-21-2019
    Location
    Bucharest
    MS-Off Ver
    2019
    Posts
    8

    Re: Sort dates and hours chronological

    It worked!!

    Thank you very much and wish you all the best!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. pivot table doesn't show dates in chronological order
    By danielpak in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-22-2019, 12:28 PM
  2. Chronological Dates In TextBoxes On UserForm
    By yoshi_5 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-29-2015, 09:35 AM
  3. Chronological ordering of dates in Powerpivot/ Power View
    By MasterElaichi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-08-2015, 07:50 PM
  4. [SOLVED] Compare two dates, ensure chronological
    By johnandrews in forum Excel General
    Replies: 4
    Last Post: 12-11-2013, 08:25 AM
  5. [SOLVED] check to make sure a column range of dates is in chronological order
    By UncleKevy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2013, 07:05 PM
  6. Replies: 2
    Last Post: 03-02-2013, 10:57 AM
  7. [SOLVED] Countdown to a set of dates not in chronological order - Excel 2010
    By toddjesus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2012, 04:42 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1