Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Excel dates and conditional formatting

  1. Top | #11
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    If I one has a csv file with a date format of MM/DD/YYYY, one would expect it to treat it as a date when imported. For some reason I cannot figure out, instead of saying, "that's a date, let's leave it alone and internally represent it as needed" it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".

  2. Top | #12
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    Quote Originally Posted by Gun Nut View Post
    If I one has a csv file with a date format of MM/DD/YYYY, one would expect it to treat it as a date when imported. For some reason I cannot figure out, instead of saying, "that's a date, let's leave it alone and internally represent it as needed" it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".
    This has been happening since the 90's... so like every version of Excel does this.

  3. Top | #13
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    Quote Originally Posted by bilby View Post
    Quote Originally Posted by Gun Nut View Post

    yes, it already does that... you (as the user) can simply say Date1 - Date2 and by defualt you get the number of days. regardless of format... I am talking about the visible format of the date (you can have "Saturday, June 8th, 2020", you can have "20190101"... many formats to view dates... internally, the date is represented as a numeric value.. a float where the integer portion is an offset from some fixed point in time, and the decimal is the time of day.
    There is no use seeing that number. If you do have use, then wonderful - enjoy your unix date..
    My issue is that Excel tries really really hard to force that format (unix) in your face and once it does, your data is corrupted... no easy way to get it back to human-viewable format.
    That's nonsense.

    Excel doesn't know what any of your data is; It formats it however the user tells it to.

    If the user doesn't specify a format, Excel treats it as 'General', which is a flexible format tolerant of both numeric and text data. When new data is entered, Excel tries to interpret what the user wants based on the data, and on the formats defined in adjacent cells (prioritising cells above and below). But the user always has the option to override Excel's guesses - unless you try to do something impossible, like formatting "bilby" as a number.

    If you import a text string that looks like a date, Excel will typically treat it as a string - "4/7/2019" isn't a date as far as Excel is concerned. You can convert it using the 'Text to Columns' function, which will ask you to specify what kind of date it is - this is necessary because it's not possible to tell from the data available (is this April 7, or July 4?)

    A string is not a date (even if it looks like one to you). A date in Excel is just a number. Excel doesn't care what you do with that number, or what it means to you. Only when displaying the cell does Excel apply a display format as specified by the user. If you format a positive number to display in red, or bold, or on a yellow background, it's still the same number. A date format isn't treated differently in Excel from a bold or green format.

    If you don't know what data you are handling, or what formats you are using, or if you confuse data with formats, then that's on you.
    This is completely false. Excel detects formats just fine. You can test that yourself with the import wizard... it tells you what data type it thinks each column should be, delimited or fixed length the same. The issue is what it decides to DO with the format. In my case, it often (not always) decides the date should be rewritten as a number... a number that cannot be converted back to a date... if you try, it just remains a number despite the cell format being applied.
    If Excel manages to not change your date format to something irreversible, you can change the display format from MM/DD/YYYY to YYYYYMMDD, for example, without splitting and concatenating the string... just specify the format and it works fine (as long as it hasn't already corrupted your original format).

  4. Top | #14
    the baby-eater
    Join Date
    May 2011
    Location
    Straya
    Posts
    3,776
    Archived
    1,750
    Total Posts
    5,526
    Rep Power
    37
    Quote Originally Posted by Gun Nut View Post
    In my case, it often (not always) decides the date should be rewritten as a number... a number that cannot be converted back to a date... i
    Then Excel is failing to parse the string as a date.

    What timestamp format does this happen to? Some formats might conceivably be interpreted as mathematical operations.

  5. Top | #15
    the baby-eater
    Join Date
    May 2011
    Location
    Straya
    Posts
    3,776
    Archived
    1,750
    Total Posts
    5,526
    Rep Power
    37
    Quote Originally Posted by Gun Nut View Post
    Quote Originally Posted by Gun Nut View Post
    If I one has a csv file with a date format of MM/DD/YYYY, one would expect it to treat it as a date when imported. For some reason I cannot figure out, instead of saying, "that's a date, let's leave it alone and internally represent it as needed" it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".
    This has been happening since the 90's... so like every version of Excel does this.
    Excel doesn't use Unix time to store date values.

  6. Top | #16
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    Quote Originally Posted by bigfield View Post
    Quote Originally Posted by Gun Nut View Post
    Quote Originally Posted by Gun Nut View Post
    If I one has a csv file with a date format of MM/DD/YYYY, one would expect it to treat it as a date when imported. For some reason I cannot figure out, instead of saying, "that's a date, let's leave it alone and internally represent it as needed" it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".
    This has been happening since the 90's... so like every version of Excel does this.
    Excel doesn't use Unix time to store date values.
    Ya, if it were unix time then it could be converted back.... It just looks like that range of numbers.

    Quote Originally Posted by bigfield
    Some formats might conceivably be interpreted as mathematical operations.
    This is conceivable... I wonder if I run into this type of format more often than most: "MM-DD-YYYY". Subtraction... but still, the resulting values are HUGE... and not possibly a result of subtracting a 4 digit number from a two digit number... but a good thought... and I guess possible. Worth paying attention to next time it happens.

  7. Top | #17
    Fair dinkum thinkum bilby's Avatar
    Join Date
    Mar 2007
    Location
    The Sunshine State: The one with Crocs, not Gators
    Posts
    21,626
    Archived
    10,477
    Total Posts
    32,103
    Rep Power
    82
    Quote Originally Posted by Gun Nut View Post
    If I one has a csv file with a date format of MM/DD/YYYY, one would expect it to treat it as a date when imported.
    Why?

    .csv means 'comma delimited text'. You should expect it to be treated as text, and interpreted according to whatever tools you use to cast that text as a different data type.
    For some reason I cannot figure out, instead of saying, "that's a date, let's leave it alone and internally represent it as needed"
    Internally, Excel doesn't have any 'date' data types. It stores all data as either 'number' or 'text'. (Although for memory management purposes, it uses a few different numeric data types).
    it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    In Excel, any single precision floating point number could be a date and time. Or not. An Integer could be a date, or not. It's up to the user to decide - by applying a 'date' format to cells whose numerical content represents a date.
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".
    Applying a 'date' format to a cell containing a single precision floating point number will display that number as a date/time in accordance with the formatting applied. Applying a 'date' format to a cell that contains text, will be ignored - because text cannot be a date, as far as Excel is concerned.

    All computers are electronic idiots. The do EXACTLY what you tell them to do - but they don't EVER care if your instructions are stupid. Excel doesn't know what you want. It doesn't know whether 26 is your age in years, the number of widgets you sold today, the day of the month, the price of widgets in dollars, or the date 'January 26th, 1900'.

    So you need to tell it. If you don't tell it, it will assume that nothing's changed. If you tell it, but don't know you told it (for example because you run a data import utility or wizard that casts text data from a .csv file to a different data type), then you can expect to become confused.

    If you use a tool without understanding what it does, it is not the fault of the tool if the result isn't what you imagined it might be.

  8. Top | #18
    Fair dinkum thinkum bilby's Avatar
    Join Date
    Mar 2007
    Location
    The Sunshine State: The one with Crocs, not Gators
    Posts
    21,626
    Archived
    10,477
    Total Posts
    32,103
    Rep Power
    82
    Quote Originally Posted by Gun Nut View Post
    Quote Originally Posted by bilby View Post
    Quote Originally Posted by Gun Nut View Post

    yes, it already does that... you (as the user) can simply say Date1 - Date2 and by defualt you get the number of days. regardless of format... I am talking about the visible format of the date (you can have "Saturday, June 8th, 2020", you can have "20190101"... many formats to view dates... internally, the date is represented as a numeric value.. a float where the integer portion is an offset from some fixed point in time, and the decimal is the time of day.
    There is no use seeing that number. If you do have use, then wonderful - enjoy your unix date..
    My issue is that Excel tries really really hard to force that format (unix) in your face and once it does, your data is corrupted... no easy way to get it back to human-viewable format.
    That's nonsense.

    Excel doesn't know what any of your data is; It formats it however the user tells it to.

    If the user doesn't specify a format, Excel treats it as 'General', which is a flexible format tolerant of both numeric and text data. When new data is entered, Excel tries to interpret what the user wants based on the data, and on the formats defined in adjacent cells (prioritising cells above and below). But the user always has the option to override Excel's guesses - unless you try to do something impossible, like formatting "bilby" as a number.

    If you import a text string that looks like a date, Excel will typically treat it as a string - "4/7/2019" isn't a date as far as Excel is concerned. You can convert it using the 'Text to Columns' function, which will ask you to specify what kind of date it is - this is necessary because it's not possible to tell from the data available (is this April 7, or July 4?)

    A string is not a date (even if it looks like one to you). A date in Excel is just a number. Excel doesn't care what you do with that number, or what it means to you. Only when displaying the cell does Excel apply a display format as specified by the user. If you format a positive number to display in red, or bold, or on a yellow background, it's still the same number. A date format isn't treated differently in Excel from a bold or green format.

    If you don't know what data you are handling, or what formats you are using, or if you confuse data with formats, then that's on you.
    This is completely false. Excel detects formats just fine. You can test that yourself with the import wizard... it tells you what data type it thinks each column should be, delimited or fixed length the same. The issue is what it decides to DO with the format. In my case, it often (not always) decides the date should be rewritten as a number... a number that cannot be converted back to a date... if you try, it just remains a number despite the cell format being applied.
    If Excel manages to not change your date format to something irreversible, you can change the display format from MM/DD/YYYY to YYYYYMMDD, for example, without splitting and concatenating the string... just specify the format and it works fine (as long as it hasn't already corrupted your original format).
    The import wizard is a utility that (amongst other things) casts data into different formats in the destination than were present in the source. Its sole reason for existence is to do more complex things than simply importing the raw data. It's a powerful tool - and if you don't understand what it's doing, you can expect to be surprised.

    Nothing in Excel is irreversible. If you ask for a change that you don't know how to reverse, that's on you.

  9. Top | #19
    Elder Contributor
    Join Date
    Feb 2001
    Location
    Located 100 miles east of A in America
    Posts
    24,094
    Archived
    42,473
    Total Posts
    66,567
    Rep Power
    100
    Quote Originally Posted by Gun Nut View Post
    Quote Originally Posted by bigfield View Post
    Excel doesn't use Unix time to store date values.
    Ya, if it were unix time then it could be converted back.... It just looks like that range of numbers.

    Quote Originally Posted by bigfield
    Some formats might conceivably be interpreted as mathematical operations.
    This is conceivable... I wonder if I run into this type of format more often than most: "MM-DD-YYYY". Subtraction... but still, the resulting values are HUGE... and not possibly a result of subtracting a 4 digit number from a two digit number... but a good thought... and I guess possible. Worth paying attention to next time it happens.
    If you are going to whine about it, how about a screen shot or something. You are describing something bizarre and unusual.

  10. Top | #20
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    Quote Originally Posted by bilby View Post
    Why?

    .csv means 'comma delimited text'. You should expect it to be treated as text, and interpreted according to whatever tools you use to cast that text as a different data type.
    No, it means Comma delimited values (the v is the hint)
    Internally, Excel doesn't have any 'date' data types. It stores all data as either 'number' or 'text'. (Although for memory management purposes, it uses a few different numeric data types).
    it instead goes, "That's a date, let me convert it to a unix date format, and then treat it as an integer that never represented a date in the first place".
    In Excel, any single precision floating point number could be a date and time. Or not. An Integer could be a date, or not. It's up to the user to decide - by applying a 'date' format to cells whose numerical content represents a date.
    Attempting to take the integer and convert it back to a readable date does not work. That is what I mean by "corrupting your data".
    Applying a 'date' format to a cell containing a single precision floating point number will display that number as a date/time in accordance with the formatting applied. Applying a 'date' format to a cell that contains text, will be ignored - because text cannot be a date, as far as Excel is concerned.

    All computers are electronic idiots. The do EXACTLY what you tell them to do - but they don't EVER care if your instructions are stupid. Excel doesn't know what you want. It doesn't know whether 26 is your age in years, the number of widgets you sold today, the day of the month, the price of widgets in dollars, or the date 'January 26th, 1900'.
    You're mistaken... perhaps you have little experience with importing data into Excel. I have 30+ years of it... It is simple to see for yourself that Excel "guesses" what your source data types are. Simply make your own CSV with text, numbers, and dates in a common format. You can even name it as a CST if you want to continue to call it the worng thing - Excel wont care. Import the CSV (not by just launching excel through the CSV file associaion... launch the import wizard). The wizzard will walk you through the import process, and one of the steps is identifying the data type for each column. It guesses the datatype for you, but you can override it.
    Are you also unfamiliar with Regular Expressions (RegEx)? its a filtering / sorting / parsing language.. people use it in their scripting to determine if a string of characters is a SSN, a Credit Card number, a phone number, a zip code... if it looks like a proper name (proper case - that's when each word begins with a capitol letter)... anyway, computers are dumb without programming, but Excel has sufficient programming to leverage that sort of intelligence and makes a pretty good effort to understand the difference between text, integers, floats, and dates.
    So you need to tell it. If you don't tell it, it will assume that nothing's changed. If you tell it, but don't know you told it (for example because you run a data import utility or wizard that casts text data from a .csv file to a different data type), then you can expect to become confused.
    Casting is completely different and unrelated to data type detection. Casting is a check to see if two classes of objects are compatible... usually because one inherits the other.
    If you use a tool without understanding what it does, it is not the fault of the tool if the result isn't what you imagined it might be.
    ok kid, whatever you say. I'd be embarrassed if I were you (and didn't lose my humility in the processes of becoming you).

Posting Permissions

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