+ Reply to Thread
Results 1 to 9 of 9

Excel Date Format

  1. #1
    Forum Contributor
    Join Date
    07-18-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    192

    Excel Date Format

    Hi Friends

    I am having issue with formatting as date. The report came from system doesn't have proper date format. I have attached the example data.

    Can you please help with the formula to fix this one?

    Thanks a lot

    Arpan
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Date Format

    The dates look fine. What's the problem? Be specific.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    07-18-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Excel Date Format

    I want in the format dd/mm/yyyy only...no time stamp

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Date Format

    1) Select a column of dates, start with column C
    2) Goto DATA > TEXT TO COLUMNS > Delimited > [x] Tab (only, deselect all others) > NEXT
    3) on the "step 3" window, select DATE: MDY and click FINISH


    Now that whole column is fixed. Repeat with the others.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Date Format

    Wait, if you want the dates removed then do this:

    1) put the number 1 in an empty cell
    2) COPY that cell
    3) Now select ALL of the date cells, A2:C283
    4) Right-click and PASTE SPECIAL > MULTIPLY > OK
    5) You will see all 5 digit numbers. Format those cells a DATE> dd/mm/yyyy

  6. #6
    Forum Contributor
    Join Date
    07-18-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Excel Date Format

    Hi there

    I have tried the steps above but didnt work for some reason. I am not sure why is it not working?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Date Format

    Ignore post #4. See post $5.

  8. #8
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Excel Date Format

    Problem for system date format. You can do as per below.
    > Go to Start
    > Click on "Control Panel"
    > Open "Regional and language options"
    > In Regional options : Clcik on "Customize" > Click on "Date" tab & change as in "short date format" : dd/MMM/yyyy
    and "long date format" : dddd, MMMM dd, yyyy
    Clcik on "apply" & "ok"

    After that :
    Try with in "D2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copy paste as per range down & right


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  9. #9
    Forum Contributor
    Join Date
    07-18-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Excel Date Format

    Thanks a lot ...it works fine..

+ 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. [SOLVED] How to convert the unix date format to excel date format
    By sivaksekar in forum Excel General
    Replies: 1
    Last Post: 08-15-2016, 03:33 AM
  2. i have to convert the csv file date format to excel date format
    By arindamsenaxa in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-17-2015, 03:37 AM
  3. Date Format Of User PC Changes Display format date of Excel File after update
    By waihar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2014, 03:09 AM
  4. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  5. convert SAP date format to excel date format
    By togyika in forum Excel General
    Replies: 2
    Last Post: 10-26-2011, 05:29 AM
  6. Not able to change date format into proper excel date format
    By excel5111987 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2011, 08:19 AM
  7. Replies: 1
    Last Post: 03-30-2005, 02:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1