Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Excel dates and conditional formatting

  1. Top | #1
    Veteran Member Brian63's Avatar
    Join Date
    Jan 2001
    Location
    Michigan
    Posts
    1,066
    Archived
    8,911
    Total Posts
    9,977
    Rep Power
    70

    Excel dates and conditional formatting

    I have been working on a project of compiling my job-related production numbers for each day into an Excel 2013 spreadsheet, and am largely a beginner/novice on Excel who is unable to figure out certain formatting preferences. Been watching some videos online and learning about it more, but have not found one that precisely addresses what I am looking for. They may add up to it, but I have not been able to sort it out myself.

    Basically, going down through column A are all the dates of the year 2019. Whenever it is the current date, I want the following to occur:

    1. Reformat the cells in that row from column A to column X only, not all the cells in that row (I will determine later my preferred format style in regards to fill/font/border).
    2. Increase the row height for all cells in the =today() row so that all the cells in the current date's row are bigger and harder to miss.

    I have been playing around with tools to get that 1st objective, and am close but not there yet. That 2nd one though is not listed as an option under the conditional formatting feature, and nobody that I have talked to seems to have any other leads on how to do it. Anyone here? Thanks.

  2. Top | #2
    Veteran Member James Brown's Avatar
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    2,876
    Archived
    5,844
    Total Posts
    8,720
    Rep Power
    55
    This link seems to cover Step 1:

    How to Highlight an Entire Row Based on Date in Excel



    For Step 2, I believe you can format the entire spreadsheet to have cells expand automatically depending on the size of the font that you selected in Step 1. That should make the day's row bigger.

  3. Top | #3
    Veteran Member Brian63's Avatar
    Join Date
    Jan 2001
    Location
    Michigan
    Posts
    1,066
    Archived
    8,911
    Total Posts
    9,977
    Rep Power
    70
    On point 1, thank you for the link. I have come across some similar articles and videos. Actually I have a "drawing board" sheet I made with some random gibberish dates and data and gotten it to work exactly how I want. When I try to apply the same instructions to my actual working sheet though, something trips it up and I do not know what. It is probably something pretty simple I am overlooking, and I just need to go through some 101 tutorials to learn the program better.

    On point 2, that sounds like a good idea. I will see about that too. Thanks.

  4. Top | #4
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    If you have different formatting rules for different date ranges, and those ranges "touch" (i.e. 1/1/2019 - 1/1/2020 and 1/1/2020 - 1/1/2021) then the conditional formatting will generate unexpected results.

  5. Top | #5
    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 Brian63 View Post
    On point 1, thank you for the link. I have come across some similar articles and videos. Actually I have a "drawing board" sheet I made with some random gibberish dates and data and gotten it to work exactly how I want. When I try to apply the same instructions to my actual working sheet though, something trips it up and I do not know what. It is probably something pretty simple I am overlooking, and I just need to go through some 101 tutorials to learn the program better.

    On point 2, that sounds like a good idea. I will see about that too. Thanks.
    Conditional formatting gets odd with how it interprets the equations. Major pain in the butt.

    Start in the first row.

    For the column with the Dates, do a conditional formatting "Format only cells that contain" -> Equal To ->=TODAY()
    In columns B to X (assuming A has the dates) in the first row, you need to "Use a formula to determine..." -> =$A*insert first row number here*=TODAY()

    No quote marks!!! Excel might try to insert them arbitrarily, remove them if they show up!!!

    Copy and paste the formats into the remaining rows.

    For Row Height changes, that needs to be VBA. Changing the fill color will make it stand out, making the row height change unnecessary.

  6. Top | #6
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    On a very related topic, why does Excel mutilate dates by converting them to some Unix number offset from an arbitrary point in history, and then be incapable of converting them back? This happens to me all the time. The date "1/1/2019" (for example) might suddenly become "19845625". But then if you explicitly try and format the cell back to datetime, it can't. Who ever wants to convert dates into "minutes past who cares" and then never be able to convert back? Pisses me off.

  7. Top | #7
    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
    On a very related topic, why does Excel mutilate dates by converting them to some Unix number offset from an arbitrary point in history, and then be incapable of converting them back? This happens to me all the time. The date "1/1/2019" (for example) might suddenly become "19845625". But then if you explicitly try and format the cell back to datetime, it can't. Who ever wants to convert dates into "minutes past who cares" and then never be able to convert back? Pisses me off.
    It makes it possible to do easy calculations. 6/21/19 - 3/1/19 equals how many days, would be a bitch to code a soln to. 43637 - 43525 equals 112 days and quite simple.

    Seeing that spreadsheets are huge in accounting, where dates are important... seems like quite the obvious thing to do. Also, the point is hardly arbitrary as it is likely a result of the older pre-Y2k days when the year in the system was a two digit number with a 19 plastered in front of it. So that is why the date equaling 1 in Excel is 1/1/1900.

  8. Top | #8
    Veteran Member
    Join Date
    Oct 2018
    Location
    Colorado
    Posts
    1,565
    Rep Power
    5
    Quote Originally Posted by Jimmy Higgins View Post
    Quote Originally Posted by Gun Nut View Post
    On a very related topic, why does Excel mutilate dates by converting them to some Unix number offset from an arbitrary point in history, and then be incapable of converting them back? This happens to me all the time. The date "1/1/2019" (for example) might suddenly become "19845625". But then if you explicitly try and format the cell back to datetime, it can't. Who ever wants to convert dates into "minutes past who cares" and then never be able to convert back? Pisses me off.
    It makes it possible to do easy calculations. 6/21/19 - 3/1/19 equals how many days, would be a bitch to code a soln to. 43637 - 43525 equals 112 days and quite simple.

    Seeing that spreadsheets are huge in accounting, where dates are important... seems like quite the obvious thing to do. Also, the point is hardly arbitrary as it is likely a result of the older pre-Y2k days when the year in the system was a two digit number with a 19 plastered in front of it. So that is why the date equaling 1 in Excel is 1/1/1900.
    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.

  9. Top | #9
    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
    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.
    Yeah, none of that is making any sense. How can it corrupt data and be hard to remodify?

  10. Top | #10
    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 Jimmy Higgins View Post
    Quote Originally Posted by Gun Nut View Post
    On a very related topic, why does Excel mutilate dates by converting them to some Unix number offset from an arbitrary point in history, and then be incapable of converting them back? This happens to me all the time. The date "1/1/2019" (for example) might suddenly become "19845625". But then if you explicitly try and format the cell back to datetime, it can't. Who ever wants to convert dates into "minutes past who cares" and then never be able to convert back? Pisses me off.
    It makes it possible to do easy calculations. 6/21/19 - 3/1/19 equals how many days, would be a bitch to code a soln to. 43637 - 43525 equals 112 days and quite simple.

    Seeing that spreadsheets are huge in accounting, where dates are important... seems like quite the obvious thing to do. Also, the point is hardly arbitrary as it is likely a result of the older pre-Y2k days when the year in the system was a two digit number with a 19 plastered in front of it. So that is why the date equaling 1 in Excel is 1/1/1900.
    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.

Posting Permissions

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