How can I compare two date columns to make sure they are within a specific timeline and add a column showing true or false?

[OkieChic]

I am not very experienced with this. I have a spreadsheet with student names and two date columns. One column is the consent date and the other is the evaluation date. We have a timeline we have to follow. This is where it gets tricky. I have a set of 60 days and the two columns must have dates that fit between each set of the 60 timeline dates. I pull the report from my application and then need to run this as a macro to filter out the ones that are outside my 60 sets of dates.

Example:
Student name Consent Date Evaluation Date
John Doe August 4 October 11
I need a new column to tell me if August 4 and October 11 fit between the dates on my 60-day timeline calendar dates. Remember the 60-day timeline is not exactly 60 days.
My 60-day calendar dates are arranged with a start date and an end date. 60 sets of dates start/end. the date ranges are random because we have to take into consideration weekends, holidays, snow days. The 60-day calendar changes every year, so I would like to be able to update the calendar and not have to redo the entire macro to whatever it is every year. Most things I find only work with one date or a greater than or a timeline and I am working with multiple criteria that need to run on each line for each student's set of dates.
I am just not sure where to start. I can post a screenshot of the timeline we use. If that would help. I hope this makes sense. Sorry, I am new to this.

 

[queuesmef]

I would set up an evaluation column with the date you are comparing it to (you'll need 2 columns for each evaluation; 1 for the date, the other for the calculation). My example should be attached below: the formula takes the inputted date in Col E and compares it to Col B & Col C. In Column H i used a little know formula DateDif in Excel....it calculates the days between dates. Google it, it's fairly useful.

 

[OkieChic]

I would set up an evaluation column with the date you are comparing it to (you'll need 2 columns for each evaluation; 1 for the date, the other for the calculation). My example should be attached below: the formula takes the inputted date in Col E and compares it to Col B & Col C. In Column H i used a little know formula DateDif in Excel....it calculates the days between dates. Google it, it's fairly useful.
View attachment 46525

Thank you. I did look up the datedif, but wasn't sure if it would work for what I need. I will try it and see if it gives me the results we need. My only issue is each line has to be checked against all 60 dates in my timeline not just one date. I am not sure how to get each line to check this.

If the consent date is any of the dates in red then the evaluation date must be the black date under the red date or before not after. If the red date son the consent doesn't match the black date underneath then it needs to flag as false which is out of the timeline for that student. See my date timeline that I need the spreadsheet to use the date timeline to check the dates of the students. See image.

 

[queuesmef]

I need to see example of what it looks like if Solved. I may not be getting the jist of this. But Im trying to add a row that would calculate the number of days from yourEval Date. An IF formula can help highlight if it is over the 60 Days. Thats what i was using the datedif for...to calculate number of days in between.

 

[queuesmef]

I need to see example of what it looks like if Solved. I may not be getting the jist of this. But Im trying to add a row that would calculate the number of days from yourEval Date. An IF formula can help highlight if it is over the 60 Days. Thats what i was using the datedif for...to calculate number of days in between.
View attachment 46540

in the cell between the date calculation (Example Above the 74) I put in the following formula to compare if the date is LESS than or EQUAL to 60....if it is the "within" else it is "FLAG". the formula I put in Cell A3 = " =IF(A4<=60,"WithIn","FLAG")". you could also change it so that WITHIN doesn't print and you are left with all FLAGs (once that didn't meet under 60 days). your format may not be the best layout for this.