Results 1 to 7 of 7

Thread: Modify date

  1. #1

    Modify date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    Im using excel 2010.
    i have a cell with the following data 20150101
    how can i make the date look better?

    Thanks

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    If the text/number in cell A1 try this formula
    Code:
    =DATE(LEFT(A1;4);MID(A1;5;2);RIGHT(A1;2))
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    An alternative try:

    =RIGHT(A1,2)&"."&MID(A1,5,2)&"."&LEFT(A1,4)

    =RIGHT(A1,2)&"-"&MID(A1,5,2)&"-"&LEFT(A1,4)

    =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

  4. #4
    Thanks guys,

    well its not in A1unfortunately

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by kobiv View Post
    well its not in A1unfortunately
    So good. Can and in G17
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Quote Originally Posted by navic View Post
    So good. Can and in G17
    actually i start typing and got distracted so... anyway got that. still no magic.

    THANKS FOLKS

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    You can convert this number into real Excel dates (in fact, if you have a column of dates like this you can convert them all at once):
    Select the numbers you want to convert (only a single column at a time), then in the Data part of the ribbon, find Text to Columns in the Data Tools section, and click it.
    In Step 1 of the dialogue box, choose Delimited, click Next, in step 2 remove all ticks from all boxes, click Next.
    In step 3 choose Date then choose YMD in the dropdown, click Finish.
    Now you have real dates, you can format them as you wish.

Posting Permissions

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