+ Reply to Thread
Results 1 to 4 of 4

Date help in excel

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    Excel 2017
    Posts
    1,009

    Date help in excel

    In column B the format is Custom and dd/mm/yyyy. The cell appears in that format which is great - for example today it shows as 24/10/2013. However, when I click the cell and look at the formula bar it displays as mm/dd/yyyy. How can I fix this so that it shows as dd/mm/yyyy in the formula bar as well?
    Last edited by ScabbyDog; 10-24-2013 at 04:52 PM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Date help in excel

    The cell format is merely cosmetic and for display purposes only. Excel stores the value as a number (date) in mm/dd/yyyy format which is what reflects in the formula bar

    If you change this to 24/10/2013 in the formula bar, the cell will not return a date per se (it wont recognize it as a numerical value) and you will not be able to use this date for further calculations if any. Are you ok with that?

  3. #3
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    Excel 2017
    Posts
    1,009

    Re: Date help in excel

    Only way around it then is to use a helper column which would have =TEXT(A1,""dd/mm/yyyy")
    I then would copy the helpercolumn and paste the values into column A and then format column A to date dd/mm/yyyy. It works. Is there not an easier way surely though?

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,908

    Re: Date help in excel

    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

+ 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. Replies: 5
    Last Post: 08-13-2017, 04:29 AM
  2. [SOLVED] How to convert exported text date to desired date format with excel vba.
    By sktneer in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-03-2013, 01:08 AM
  3. Replies: 0
    Last Post: 03-21-2013, 12:22 PM
  4. Replies: 3
    Last Post: 07-11-2012, 10:03 AM
  5. Replies: 1
    Last Post: 04-18-2012, 05:49 PM

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