Advanced date logic in Google Spreadsheets

Question

I have the following 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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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"

Answer 5

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.

Answer 6

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