Results 1 to 4 of 4

Thread: I don't want julian date conversion, but EXCEL is doing it anyway

  1. #1
    Neophyte hairymonster's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office 365

    I don't want julian date conversion, but EXCEL is doing it anyway



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

    Hi everyone,

    I'm trying to enter dates into a spreadsheet, but excel is treating my keyed date as a Julian date and converting it as such.

    I have formatted the field as a date field to be dd/mm/yyyy

    So, I would expect to be able to key 251116 or 25112016 and see this convert to 25/11/2016. Instead it converts it to 11/07/2587!!!

    I don't know what I have done to make this happen. Any ideas please?

    Thanks - Philip

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,554
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    Alas, things are not so simple in excel when dealing with time and dates . Read this to understand how XL treats time and date. Formatting will not help in this case.
    One easy workaround
    -enter 25112016 in a cell - select the cell - Data tab - Text to columns - Next -Next - In the column data format select "date" and the wanted format ( in this case DMY) - Finish
    You can apply this to an entire column
    Thank you Ken for this secure forum.

  3. #3
    Neophyte hairymonster's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office 365
    Thanks very much.

    Am I imagining it, or did Excel ever allow you to key a date in without slashes i.e. 140718 and it would format it as 14/07/18 or 14/07/2018 if the cell was formatted as a date?

    Philip

  4. #4
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,554
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Probably with some VBA.
    Thank you Ken for this secure forum.

Posting Permissions

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