EXCEL help needed -- why does it do this?!

Discussion in 'Off Topic' started by RickAgresta, Aug 12, 2018 at 9:02 PM.

  1. RickAgresta

    RickAgresta Peanut, leader of the Peanutty Forces

    Messages:
    19,966
    Likes Received:
    13,266
    Trophy Points:
    288
    I follow what's taking place when the ' or the " are added to the Date/Time value, but by all that makes sense, why is the highlighted 3rd entry coming in at a count of 9 characters? the following entry would be 07/01/2018 15:10:00, which LEN tells me is 16 characters…?!

    Code:
    Date,Time, same cell
    format, varied entries. LEN COL_A    Comment
    "07/01/2018 12:00:00"        21      Leading, ending "
    07/01/2018 15:00:00          19      Leading '
    07/01/2018 15:00:00          9        ---
    "07/01/2018 15:00:00         20      Leading "
    
     
  2. headcronie

    headcronie Greyscale. Nuff Said. Super Moderator

    Messages:
    13,664
    Likes Received:
    2,533
    Trophy Points:
    113
    Sorry, Rick! I only install this software, I am not a power user. I leave that to the professionals. :eek:
     
    scjjtt and RickAgresta like this.
  3. headcronie

    headcronie Greyscale. Nuff Said. Super Moderator

    Messages:
    13,664
    Likes Received:
    2,533
    Trophy Points:
    113
    A guess would be that without any sort of quote, it is reading " 15:00:00" for the character length. Spaces are counted, but why it would start at a space I can't begin to understand.

    The / might count as some sort of escape character, and could be causing all that data to be ignored. That's all guessing. No knowledge of it on my part.
     
    scjjtt and RickAgresta like this.
  4. raspabalsa

    raspabalsa Brain stuck BogoMipping

    Messages:
    8,568
    Likes Received:
    4,791
    Trophy Points:
    288
    It follows the way you've set your date format under Regional Settings and how you input the date in Excel. Check the examples below:

    upload_2018-8-14_17-47-49.png

    If you enter the date as such, then Excel converts it to its numerical value, and counts the characters in said number. If you only input the date, then the number is an integer. If you add the time, the number will have decimals, and Excel will count them and also the decimal point.

    If under regional settings you set mm/dd/yyyy, then you must enter the date as such for Excel to count it as number. This is useful when you want to run operations (sums, substractions, etc) on dates. Note that when Excel takes the date as a number, then it will justify it to the right, otherwise it will keep it justified to the left, as it does with text strings.

    I fight this every day. In Ecuador, Perú, and Bolivia, people write date as dd/mm/yyyy, in Colombia they write it as mm/dd/yyyy. I've been trying to enforce the yyyy-mm-dd format for years, with little success so far. It's worse when several people from various countries colaborate in the same report, then the date column becomes a thorough mess.

    EDIT: Corrected date format in image above, changed hyphen to slash.
     
    Last edited: Aug 14, 2018 at 6:48 PM
  5. RickAgresta

    RickAgresta Peanut, leader of the Peanutty Forces

    Messages:
    19,966
    Likes Received:
    13,266
    Trophy Points:
    288
    all entries made the same format LEN says:
    07/01/2018 15:10:00 16
    07/01/2018 15:00:00 9

    this, in particular, is what I'm not understanding. at a glance, it seems that zeros are not always treated the same, almost as if the zeros for 3 PM are not significant figures. fwiw, to the best of my knowledge, the Regional settings for my Excels are default values.

    as it turned out, though, I didn't need to split out the date from the time....
     
  6. headcronie

    headcronie Greyscale. Nuff Said. Super Moderator

    Messages:
    13,664
    Likes Received:
    2,533
    Trophy Points:
    113
    Thank you, Raspy for your insight! It's hard to change how you read and write dates. Even our general speech, it is August 15th, 2018. I know I can say the 15th of August, 2018. It seems awkward. I feel for your struggle though!
     
    lelisa13p likes this.

Share This Page