+ Reply to Thread
Results 1 to 4 of 4

How to format numbers as date

  1. #1
    Registered User
    Join Date
    06-05-2018
    Location
    Croatia
    MS-Off Ver
    Mac office version 16.13.1
    Posts
    2

    How to format numbers as date

    Hi everybody!

    I hope someone can help me with a problem I came across.
    I have a set of data that are supposed to be dates but are originally written in a way that excel cannot format it as a date (at least I don't know how to).
    Eg it is written 1062018 (that is supposed to be 1st June 2018) or 28112017 (28th November 2017) or 20042018 (20th April 2018).
    I would really appreciate it if someone could teach me how to make those cells to be in a date format (any format of date is fine) just so that I am later able to sort and filter the data by date.

    Thank you all so much in advance!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How to format numbers as date

    Hello and welcome to the forum.

    Try this:

    =DATE(RIGHT(A1,4),MID(RIGHT("0"&A1,8),3,2),LEFT(RIGHT("0"&A1,8),2))

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: How to format numbers as date

    Hi korisnickoime

    for most problems there is a formula. in this case the following will work
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I also created a example file to show it works on your dates
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-05-2018
    Location
    Croatia
    MS-Off Ver
    Mac office version 16.13.1
    Posts
    2

    Re: How to format numbers as date

    Thank you both very much for your replies and for the welcome.

    I tried both suggestions but for me Roel's suggestion did the trick.
    I am not implying that 63falcondude's suggestion is incorrect, maybe I was repeatedly entering something wrong but I just wasn't able to make it work for me.

    Thank you again and have a nice day!

+ 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. Numbers displayed in date format
    By Trygve67 in forum Excel General
    Replies: 4
    Last Post: 05-27-2014, 04:50 AM
  2. [SOLVED] Converting numbers to date format
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-29-2012, 01:59 PM
  3. convert series of numbers to date format
    By marinarivas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2012, 05:35 PM
  4. [SOLVED] Converting 16 digit numbers to a date format
    By koochandkai in forum Excel General
    Replies: 4
    Last Post: 05-17-2012, 11:46 AM
  5. Coverting numbers to date format
    By oparman in forum Excel General
    Replies: 4
    Last Post: 09-29-2009, 11:07 AM
  6. Converting numbers into date format...
    By robbiebrown34 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-13-2007, 11:46 AM
  7. [SOLVED] Changing date serial numbers to date format
    By rdunne in forum Excel General
    Replies: 1
    Last Post: 04-14-2005, 08:06 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