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 use a spreadsheet in my medical office for referrals. It includes places to fill in these three areas: date faxed, date of the appointment, date the appointment office notes were received. The problem is that sometimes people don't go to the appointments. I want the 'date of the appointment' box to highlight in yellow if it has been 1 month since the 'date faxed' and no appointment date has been entered. I also want to have the 'date of the appointment' box changed to a red highlight if it has been 6 months since the 'date faxed' and no appointment date has been entered. How would I go about doing this on the Google drive spreadsheet?

share|improve this question
add comment

2 Answers 2

Google spreadsheet's conditional formatting is much more limited than Excel. You can only change a cell's color based on its own value, not the value of other cells. So you'll have to add a new column.

Let's say that Column A is the Date Faxed, Column B is Appointment Date.

  1. Create Column C called "Days Elapsed". Go to cell C2 and give it a formula of
    =if(isblank(B2), today()-A2, "")
    What that does is calculate the Days Elapsed since Date Faxed, or if there's a value for Appointment Date, then leave it blank.

  2. Fill Down the formula from C2 to the bottom of the sheet.

  3. Select the entire Column C. Format > Conditional formatting. Add rules:

    a. if greater than 180, background red
    b. if greater than 30, background yellow

share|improve this answer
add comment

Assuming:

  1. date faxed is in A1
  2. date of the appointment is in B1
  3. date the appointment office notes were received is in C1

then with New Google Sheets enter:

in B1 Custom formula is =today()-30>A1
in C1 Custom formula is =and(isblank(C1),today()-180>A1)

with Background to suit.

I have approximated one month as 30 days and six months as 180 days but it might be possible to be more precise if required.

share|improve this answer
add comment

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.