Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the folowing situation:

Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date.

  1. What is a way to use conditional formatting to get Google Spreadsheets to highlight cells that are up to 7 days away from current date?

  2. How can I get the spreadsheet to sort the sheet based on ascending dates in the range I want to sort? I am trying to do this but all it does is sort all the data including column titles which is not what I want.

share|improve this question
up vote 10 down vote accepted


Use Conditional Formatting with three rules: (Format -> Conditional formatting)

  1. "Date is before" "in the past week" -> red
  2. "Date is after" in the past week" -> green
  3. "Date is" "in the past week" -> orange

This will colour all dates more than a week away in green, all dates coming in the next week orange and the remainder of the dates in red. Empty cells will be left alone.

share|improve this answer
Wow! this was so simple! i had no idea the rules worked this way... thanks! – Andrew Davis Jun 28 '12 at 17:44

The accepted answer mentions colours that are not mentioned in the question and only looks back. Also "Date is before" "in the past week" does not highlight dates within seven days of today. I suggest a Conditional formatting Custom formula is of the type:


where six days before today, today and six days after today should all be highlighted.

share|improve this answer
  1. I don't know if there's a way to do that, except by writing some scripts (which can get pretty complex and probably not so reliable)

There's a simpler workaround, though. You could:

  • add a new column to the table, next to the date
  • store the difference (in days) between that date and today (for example, if the date cell in the first row is C2, you could enter the following formula: =C2-now())
  • format the difference column depending on its value (i.e. if it's less than 7)

This does not format the date cells, but it could be an acceptable workaround.


  • select the entire range you want sorted (including column titles)
  • from the menu select Data -> Sort Range...
  • tick the "Data has header row" check box
  • select the sort column from the "sort by" drop down
  • click "Sort"
share|improve this answer
thanks. i will try this. – Andrew Davis Apr 11 '12 at 19:22
thanks for your answer. I decided to go with a simpler approach. – Andrew Davis Jun 28 '12 at 17:45

You can use

=ArrayFormula(DAYS360(M2:M; N2:N))

where M2 and N2 hold the date you want to compare. It shows the differences in days by number between 2 dates. Then you can add a conditional to a cell like if it is a big then 7 backround red.

share|improve this answer
thanks for your answer. I decided to go with a simpler approach. – Andrew Davis Jun 28 '12 at 17:45

Use the condition that the Date is before =today()+7

share|improve this answer
This doesn't add much to the other answers. Can you elaborate on why this is different? – jonsca May 2 '15 at 21:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.