entering date
Results 1 to 6 of 6

Thread: entering date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    1,301
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default entering date

    Hi
    My computer date format as following dd/mm/yyyy (as per the date below the clock on my computer).
    I entered this date in an excel cell: 2019/09/08 (09 is sept). To my surprise, excel converted this date to the following

    08/09/2019

    Why is that?

    I thought excel should not accept 2019/09/08 as a date and keep it as it is (to the left hand side of the cell) because it should be considered as string not number, since I did not follow the format of the computer date.

    Thank you very much

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,072
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: entering date

    Quote Originally Posted by lezawang View Post
    Hi
    My computer date format as following dd/mm/yyyy (as per the date below the clock on my computer).
    I entered this date in an excel cell: 2019/09/08 (09 is sept). To my surprise, excel converted this date to the following

    08/09/2019

    Why is that?
    I am not sure why it chose that order given your computer's setting; however, if you wish to enter dates in YEAR, MONTH, DAY order, use dashes instead of slashes and I think Excel will convert it correctly. For example...

    2019-09-08
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    130
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: entering date

    Hi, What abuot
    Code:
    Cells(2, 3) = Date
    Cells(2, 3).NumberFormat = "yyyy/mm/dd"
    Last edited by mohadin; Aug 18th, 2019 at 03:45 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: entering date

    Excel is simply trying to be "helpful" it recognises 2019/09/08 as a date & converts as such.
    To stop that happening, change the cell format to text before entering the date, or put an apostrophe as the first character.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    130
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: entering date

    Quote Originally Posted by Fluff View Post
    Excel is simply trying to be "helpful" it recognises 2019/09/08 as a date & converts as such.
    To stop that happening, change the cell format to text before entering the date, or put an apostrophe as the first character.
    If so, why not:
    From the format cell > Custom > Type yyyy/mm/dd
    Last edited by mohadin; Aug 18th, 2019 at 11:15 AM.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: entering date

    My take is that the OP wants the value to remain as a string, rather than be converted to a date.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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