+ Reply to Thread
Results 1 to 8 of 8

change a number to a date

Hybrid View

  1. 10-09-2013, 05:43 PM #1
    nicko54
    nicko54 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    change a number to a date

    Hi all,

    I have a list of numbers which I want to convert to dates.

    I thought if I just went to 'format cell' - 'date' it would change it but it converts it to a random number.

    e.g. the number in the cell is 130601, which is June 1, 2013 bu when I try and change the cell format to 'date' it brings back '2257-07-27'

    Is there another way to edit this? I feel like I am missing something simple?

    Thanks
    Reply With QuoteRegister To Reply
  2. 10-09-2013, 05:45 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: change a number to a date

    What should be the result?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 10-09-2013, 05:49 PM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: change a number to a date

    Um, June 1, 2013 is 41426 in excel (using 1/1/1900 as start dt) and 130601 translates to 7/27/2257.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 10-09-2013, 05:55 PM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: change a number to a date

    Dutch 
    =RECHTS(A1;2)&"-"&DEEL(A1;3;2)&"-"&"20"&LINKS(A1;2)*1
    English 
    =RIGHT(A1,2)&"-"&MID(A1,3,2)&"-"&"20"&LEFT(A1,2)*1
    Reply With QuoteRegister To Reply
  5. 10-09-2013, 05:57 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: change a number to a date

    Try this:

    =DATEVALUE(MID(A1,3,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,2))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  6. 10-09-2013, 06:00 PM #6
    nicko54
    nicko54 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    Re: change a number to a date

    thanks oeldere! works perfectly.

    Thanks for all the posts. really appreciate it.
    Reply With QuoteRegister To Reply
  7. 10-09-2013, 06:02 PM #7
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: change a number to a date

    Thanks for the reply.

    Glad I could help.
    Reply With QuoteRegister To Reply
  8. 10-09-2013, 06:07 PM #8
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: change a number to a date

    and quick explanation on why it wasn't returning your result you wanted is excel starts counting dates at 1/1/1900 which is 1 1/2/1900 is 2 ect so the date you wanted would be like 45000 or something under excels logic which is why excel returns the year 2250 with the number you had in.
    The Truth shall set you free
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Cells that can change date depending on the number i put in
    By ANTH16 in forum Excel General
    Replies: 6
    Last Post: 08-01-2013, 08:46 PM
  2. change a number looking vs date
    By ukrockhit in forum Excel General
    Replies: 11
    Last Post: 05-19-2010, 06:54 AM
  3. Date change to number
    By minkey050705 in forum Excel General
    Replies: 1
    Last Post: 02-09-2010, 07:34 AM
  4. Change number to date
    By sioncleverly in forum Excel General
    Replies: 3
    Last Post: 03-13-2007, 12:15 PM
  5. Formula to change number into date
    By anjgoss in forum Excel General
    Replies: 4
    Last Post: 09-28-2005, 05:47 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1