Results 1 to 3 of 3

Thread: Date Question

  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    Waldorf, MD
    Posts
    47
    Thanks
    22
    Thanked 0 Times in 0 Posts

    Date Question

    I am using Excel 2016. When pasting a date into a cell and doing a format of the cell to date *3/12/2017, the month and day were transposed. For example the pasted value is 3/2/2017. After the format action, the date value was 2/3/2017. Has anyone an idea of why this happened? TIA

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,686
    Thanks
    59
    Thanked 1,064 Times in 989 Posts
    Americans were allowed to have their own date format so we never know what is the day and what is the month. Excel makes an assumption about the format you use when entering the date and when you format the date Excel uses that format instead.
    e.g.
    You enter the date and Excel assumes m/d/yyyy (2/3/2017).
    Change the cell format to d/m/yyyy and you get 3/2/2017.

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    bluegoose1776 (2017-03-10)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,188
    Thanks
    152
    Thanked 591 Times in 561 Posts
    So cell date formats that start with the asterisk * respond to the current windows regional date settings on that computer. This is useful if you share files across different regions.

    So, if I have a spreadsheet in the UK with a column of dates that are formatted with *3/14/2001 (Excel2010), these dates will appear as UK format on my system here (dd/mm/yyyy), but if I send thatworkbook to RG in the States, he will see that column of dates in mm/dd/yyyy format.

    When you save a workbook in csv format, the exported dates will be 'as displayed'. So if I open that csv file in Notepad, I would see those dates as per my UK format i.e. dd/mm/yyyy.
    ..but if you then open that csv file in Excel in the USA, it will treat any UK dates later than the 12th of the month, e.g like 25/12/2017, as text, since there is no 'month' 25 etc etc

    zeddy

  5. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    bluegoose1776 (2017-03-10),RetiredGeek (2017-03-10)

Tags for this Thread

Posting Permissions

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