Advanced date logic in Google Spreadsheets

Question

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?

Ex: 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.

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.

Thanks in advance.

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

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 3

you can use =ArrayFormula(DAYS360(M2:M; N2:N)) where M2 and n2 hold the date you want to compare it show the differences in days by number between 2 dates then you can add a conditional to a cell like if is big then 7 backround red