IF(S) Functions and Date Ranges to Create a Visual Cue

**amk144** · 02-15-2017, 11:53 AM

Apologies in advance as this is my first post and I am a novice in regards to excel functionality.

Some background: I am a research coordinator and part of our job requires that we keep track of what dates participants come in for their visits as well as making sure that we have each person scheduled for an upcoming visit. Our current system involves cross referencing our running excel spreadsheet of participant visits against third party software that tracks the 'visit window' for each participant, the range of dates that are acceptable for the visit to occur. This process is quite time consuming and I am looking for an alternative. In the past I have used date functions to predict the target visit date for a particular participant as well as conditional formatting to change the color of a cell to indicate one thing or another. I was hoping to combine these two functions to
indicate how close the current date is to the projected target date and use a changing cell color to visually cue the user that the target date is approaching or has passed.

In my mind, there would be a single cell that would change color, say green to yellow to red, depending on if a certain cell had a date in it that met certain date range criteria, i.e. green would indicate that the target date is +15 days away, yellow would indicate the target date is +/- 3 days and red would indicate the target date is -15 days past.

So my issue is that I don't know how to create this function or if it is at all possible. An additional problem is that this would need to be repeated for multiple cells that all meet different date range criteria, i.e. the date range would vary depending on the which visit they were attending (seen as columns with headers Quarterly 1,2,3 etc. and Annual in the attached spreadsheet).

I realize that this is complex and I'm sure I've failed in the explanation at some point. Any assistance that someone could lend would be much appreciated.

Thanks!

amk144

**Bernie Deitrick** · 02-15-2017, 01:09 PM

You can do (almost) anything with Excel as long as you understand the logic of what you need.

It helps to have examples, and explanations.

On your sheet 1, indicate which cell you want to have a colored fill, and indicate the reason it should be that color based on other dates. Give as many examples as you need to fully explain what your formatting scheme needs to address. And if you don't need the other sheets, remove them. They seem to have a lot of information that is superfluous to your example and table.

**amk144** · 02-15-2017, 02:53 PM

Hi Bernie,

Thanks you, comments well taken.

In Sheet1, we can use patient 130-0005, row 10, as an example. I am basing the target dates for this participant's visit on their Randomization date, 8/29/2016, seen in cell U10. Target dates for quarterly visits are every 12 weeks, see cells X10, Z10 etc. Since these dates are flexible there is a 'window' for each visit of +/- 6 weeks. The idea would be to have a specified target date for each visit, for example the target date for X10 would be U10+84 (12 weeks), target date for Z10 would be U10+168 (24 weeks) and so on for each quarterly or annual column. The user would input the actual, scheduled date in a cell in that row, say A10.
Ideally I would like to create a function that could move on from one target date to the next once the visit window has elapsed, this is the function that I am unsure of being possible.
There are two things that i'd like to do:
1. have a cell that changes color letting the user know that the date entered into A10 meets the required date range.
This function seems to be the simpler one, lets use N10 as the color indicating cell. We would input our scheduled date into A10, the function would compare this date to X10 +/-42 (6 weeks) and change N10 to green if it meets the criteria and red if it does not.
2. have a second cell that changes color based on the current date in relation to the end of the required date range.
We can use N10 as the color indicator cell again. This cell would change color based on the current date in relation to the target date, specified in X10. [Green = X10-TODAY>10], [Yellow = X10-TODAY= <11 to >-11], [Red= X10-TODAY=<-10]. The issue here is i'm not sure how this could be applied to shift to Z10 once the visit window has elapsed. Another possibility is to have the function move to Z10 once a date has been entered into X10 on the day the participant actually attends the visit and then to AB10 once a date has been input into Z10 and so on.