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?
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.
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.
Fill Down the formula from C2 to the bottom of the sheet.
Select the entire Column C. Format > Conditional formatting. Add rules:
a. if greater than 180, background red
b. if greater than 30, background yellow
date faxedis in A1
date of the appointmentis in B1
date the appointment office notes were receivedis in C1
then with New Google Sheets enter:
in B1 Custom formula is
in C1 Custom formula is
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.