+ Reply to Thread
Results 1 to 5 of 5

Convert numbers appearing as a date into actual date in order to sort data

  1. #1
    Registered User
    Join Date
    09-25-2015
    Location
    Edmonton, Alberta
    MS-Off Ver
    2007
    Posts
    7

    Convert numbers appearing as a date into actual date in order to sort data

    Hi there,

    Trying to sort via a date column from a inventory download.

    Dates are being recognized as numbers in excel and therefore won't allow me to sort by oldest to newest.

    In cell A1 I have: 12/18/2015 18:02

    Is there a way to convert this number sequence into a usable excel date format that looks the same?

    I tried using DATE, LEFT, RIGHT and MID functions but can't seem to make it work!

    Also, other threads I found related to this process are slightly different!

    Many thanks!!

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Convert numbers appearing as a date into actual date in order to sort data

    try this

    =INT(A1)

    and formatted as date

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert numbers appearing as a date into actual date in order to sort data

    In Excel dates and times are just numbers formatted to look like dates/times.

    If that entry is a true Excel date/time then it has the numeric value of 42356.7513888889.

    To see this, change the cells format to General or Number (and increase the visible decimal places).

    So, you should be able to sort on this date/time value. If you just want the date portion this formula will extract it:

    =INT(A1)

    Format as Date
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-25-2015
    Location
    Edmonton, Alberta
    MS-Off Ver
    2007
    Posts
    7

    Re: Convert numbers appearing as a date into actual date in order to sort data

    Thanks for the response!! I think we are almost there.

    The issue with INT is that it removes the hours/minutes and I need to keep those!

    Any other solutions?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Convert numbers appearing as a date into actual date in order to sort data

    Then just format the cells as date and sort. The sort will take into account the time (8 AM will show up before 5 PM of same date) but the dates will go from smallest to largest. What problem are you having?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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. Convert date to actual day of the week
    By georgewash in forum Excel General
    Replies: 2
    Last Post: 12-01-2014, 04:37 PM
  2. Sequence numbers based on date and independent of column sort order
    By bedepe in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-21-2014, 07:32 PM
  3. [SOLVED] convert cell that looks like date to an actual date
    By maacmaac in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-30-2013, 09:44 PM
  4. Change date serial to actual date numbers
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2012, 12:47 PM
  5. How to sort date by actual date and not first number!!
    By Brice112 in forum Excel General
    Replies: 2
    Last Post: 05-04-2010, 06:19 PM
  6. Convert date as text to actual date
    By andysurtees in forum Excel General
    Replies: 4
    Last Post: 01-23-2009, 03:22 AM
  7. Convert Date Value to Actual Date
    By pmorris in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-13-2007, 07:14 AM

Tags for this Thread

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