10

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.

13

Yes.

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.

3

In case you need more precise times than in the accepted answer, you can use DATEDIF to specify a certain difference between dates.

Example: format cell A1 if date in A1 is older than 10 days.

Instruction:

  1. Select cell A1
  2. Go to Format/Conditional Formatting
  3. Below "Format cells if..." choose "Custom formula is"
  4. type: =DATEDIF(A1,TODAY(),"D")>10
  • Please, change comas in formula to semicolons. It doesn't work with comas anymore. – installero Apr 19 at 7:46
2

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:

=and(A1>today()-7,A1<today()+7)  

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

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

2.

  • 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"
  • thanks for your answer. I decided to go with a simpler approach. – Andrew Davis Jun 28 '12 at 17:45
0

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.

  • thanks for your answer. I decided to go with a simpler approach. – Andrew Davis Jun 28 '12 at 17:45
-3

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

  • 3
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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