2

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?

1

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

| improve this answer | |
1

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.

| improve this answer | |

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.