How To Highlight Timestamps Over 7 Days Ago
Learn how to highlight timestamps in Google Sheets that are over seven days old using conditional formatting and custom formulas.
We have some timestamps, and we want to highlight these timestamps that are over seven days ago. So timestamps are interesting. They act very similar to dates, so we can handle them almost just like dates. So let's go up to Format , Conditional Formatting . We're going to apply our conditional formatting A to A, covering the entire column . Now there is a format cells if option that says date is before. And when we click on the options, we have today, tomorrow, yesterday, past week, month, year, and exact date. This past week is a little strange because we're not really sure if it means seven days. It could, but it's sort of highlighting a little bit wrong. Right at this moment, it's highlighting the 11th, and it's the 18th. How to Format Timestamp to Day and Month How to Format Timestamp to Day and Month How To Highlight Any Date One Week In Future
So I actually don't want that highlighted. So this is not going to work for me. Instead of format cells if date is before we're going to do a custom formula equals a1 It says a1, but it will apply it to every single cell in the a column is less than today Parentheses minus 7 and you'll notice that it does highlight the 10th that is correct But it also highlights these blank cells because that's essentially 0 which is under Today minus seven. So we need to add one extra thing here. We're going to wrap everything with an and so at the end We'll put a parentheses and we'll put a comma here. We need two things We need one that this is less than today minus seven, but we also want to make sure it is a date So we'll do is date a one in Anders Asks: Can I Highlight Whole Row if Certain Columns have text? Google Sheets Interface Changes How to Highlight Today Only in Conditional Formatting
parentheses as well and that gets us our answer So we need pretty much three formulas here is date Date, today, and, and to wrap those together, and we can highlight timestamps over seven days ago. Any particular number you want to put here, minus seven, minus fourteen, twenty eight, a hundred, a hundred days ago, anything like that. Hope you enjoy, and hope you're making your spreadsheets better. How Many Days Between Two Dates? Google Sheets Interface Changes How To Highlight Any Date One Week In Future