Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

3 Answers 3

up vote 6 down vote accepted

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.

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

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"
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 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

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

Your Answer

 
discard

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.