1. Conditional formatting using dates
Now we are going to go over Conditional Formatting using dates. Sheets has some built-in functionality for dates that are relative to the current date or you can work with static dates with the functions built in for regular numbers.
2. Localization settings
As we go through these examples, you'll notice that I’m in a country that is using the Month, Day, Year convention.
If you'd like to change this, go to File and select Settings.
3. United States
and adjust your locale setting from here and then you can work with dates in a way that makes sense to you.
4. Warehouse inventory
Our example is a table of data that represents a warehouse in the back of a shop. Every line refers to the same item but has a unique SKU based on the date on which it was manufactured. You just found out that everything manufactured in the last week needs to be sent back. Let's add formatting to these particular items.
The value of "Today" for this data is February 10, 2019, but the relative date functions you use in practice will reflect the current date.
5. Highlight your dates
The first thing that you want to do is highlight the dates that you want to format. Once you have the dates selected, you can start your conditional formatting.
6. Date is
The date is, date is before, and date is after options work around the value of today's date. They will return different results tomorrow and they would have operated differently yesterday.
The Date is option will allow you to pick one particular day or a range of dates. Since you are looking for just one week, you should use the Date is option.
7. In the past week
Next, choose "in the past week" to get the last seven days and click done.
8. Result showing in the past Week
These are the results. We've highlighted all of the items that were manufactured in the last seven days. You can print out this list, bring it to your warehouse, and pull those items out.
9. How dates and times are stored
As we move into formatting fixed dates, it is important to know that dates in spreadsheets are really just numbers formatted to look like dates. Each row in this table contains equal values.
December 31, 1999, is equal to 36,525 point 00. January 1, 2000, the next day, is one whole number more.
If you add time into the mix, it just adds a decimal value to the end to represent a portion of the day. For example, noon on December 31, 1999 would be 36,525 point 50. Any date without a stated time assumes that it's midnight, or point 00.
10. Conditional formatting with fixed dates
Using the options below the relative date options, we can use choices such as less than or greater than. These options don't move with the current date.
11. Greater than or equal to
If you format all of the dates Greater than or equal to February 1, 2019, you get this result.
12. Let's practice!
We have covered relative and fixed date formatting options. Remember that if times are added to your dates, they work in the same way. They are all really just numbers in disguise. Let's practice!