Excel dates and conditional formatting

[Brian63]

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.

 

[James Brown]

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.

 

[Brian63]

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.

 

[Gun Nut]

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.

 

[Jimmy Higgins]

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.

 

[Gun Nut]

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.

 

[Jimmy Higgins]

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.

 

[Gun Nut]

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.

 

[Jimmy Higgins]

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?

 

[bilby]

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.

 

[Gun Nut]

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".

 

[Gun Nut]

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.

 

[Gun Nut]

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).

 

[bigfield]

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.

 

[bigfield]

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.

 

[Gun Nut]

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.

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.

 

[bilby]

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.

 

[bilby]

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.

 

[Jimmy Higgins]

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.

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.

 

[Gun Nut]

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).