IF, AND, OR Function

Reginald

Hello....

I trying to figure out how to write a IF statement that would allow me to match the value in one cell to a value in another cell on a reference sheet. If that value is true it needs to check that the date issued column on that row and make sure it is 1 year within today's date. If all that matches i need the cell on my current sheet to show red. Is this possible?

"Currently Has Discipline" is where the formula will go. It will need to match any number in the SSO column. (This number is dynamic)

I want to match this to a another sheet column "SSO" ( this number is dynamic) and also use "Date Issued " Column to check that the date is within 1 year of todays date. Then have that cell on my "Currently Has Discipline" sheet turn red.

Is this possible?

Genevieve P.

Hi @Reginald

It sounds like first you need the formula in your source sheet that identifies if the current row & SSO Number is associated with the current Year or not.

As long as your "Order Number" years are always formatted as YYYY-YYYY we could use a LEFT and RIGHT function to grab the start year and end year and compare that to today's year.

For example, something like this:

=IF(AND(YEAR(TODAY()) > VALUE(LEFT([Order Number]@row, 4)), YEAR(TODAY()) <= VALUE(RIGHT([Order Number]@row, 4))), "Active", "Not Active")

This will check to see if Today's Year is greater than the first year listed, but less than or equal to the second year listed, meaning the year falls within the range of your Order Number cell. (Is this what you wanted to do?)

Once you've got that formula working, you can use a COUNTIFS in your second sheet to check and see if any rows in your source sheet are associated with the current SSO number and have "Active" in the Date Issued column, like so:

=IF(COUNTIFS({SSO Column}, SSO@row, {Date Issued Column}, "Active") >= 1, "Red", "Green")

Then you can set up Conditional Formatting based on the formula output.

Cheers,

Genevieve

Reginald

Hi Genevieve, Thank you for this and it works but Im looking for ever more detail. If the Order Number Column was lets say a "date column " and the date was "11/28/22" or "5/20/22" in a cell i need this date to not exceed one year from the date issued column. Everththing else in the formula works great and does not have to chance.

I need the date to be a rolling calander year date. "7/16/21-7/16/22" that would be the only part that would need to change with this not have the YYYY-YYYY format.

Genevieve P.

Hi @Reginald

I'm not sure I quite understand your set-up or what you're looking for. When you say "Date issued column", is this also a Date column, and you're looking to compare these two dates?

For example, will you have a Date in the "Order Number Date" column (formatted as a Date type of column), and then are you looking to compare that to the "Date Issued" column (also a Date column) to make sure that the "Date Issued" is not older than a year in comparison?

If so, you could use this formula:

=IF([Order Number Date]@row > [Date Issued]@row + 365, "Past Date", "Active")

It adds 365 days to the Date Issued (or one year) and then checks to see if the Order Number Date is greater than that, or older than a year from when the row was Issued. Does that work for you?

Reginald

HI Genevieve. I know this is confusing to you as its even more confusing to me. I think we are almost there. I have attached new screen shots of what I need to focus on.

When i have employee apply for a new position on the JobAplication -Intake Form. I would like the abiity to look in the "Currently has Discipline" column and see if that have prior discipline. This information i would like to pull from from another sheet that currently has the Discipline information on it called "Discipline Tracker Master".

I would like to use the date in the format of "xx/xx/xx" in the "date" column and the "SSO" from the SSO column to help figure out or fill in the request.

The "date" column on "Discipline Tracer Master" will determine if the Currently has Dicipline on the other sheet is red or green ( maybe).

So it would go as such: Employee fills out for a new postition, the information come into the Job Application... form, It will popluate a date in the created column, the SSO in the SSO column so on and so forth as the candidate fills this in. ( All this information will be provide by the employee). What i would not have is the current has discipline. Which already stated i would like to pull from the another sheet.

For this to work the "created" date column on Job Application..Form will have a date that should be within one year of the "date" column on the discipliin.... Tracker" sheet . If the date is today (11/29/22) on "Job App... Form" sheet and (11/29/21) shows on the other sheet this would fall within that year. This will would tell me that this employee has had discipline within the last year and there does not qualify for the position.

Somehow the SSO has to be connected between the two sheet as its a unique numbe that would repersent the ID of the employee.

Hope this breaks down what Im trying to do a little more. Thank you for your patience in working with me through this.

Paul Newcome

I think maybe we need to INDEX/MATCH the date and then nest that in an IF statement.

But first... I notice you have both number and text values in the column we are matching on. We are going to need to convert all of the rows into text values so that we have a consistent data type to match on. In both sheets you would insert a text/number column (both called "Helper" in this example) and use a formula such as this:

=SSO@row + ""

(double quotes after the plus)

You can hide this column on each sheet to help keep them tidy if needed.

Then the formula to pull in the date and make the comparison to today would be something like:

=IF(INDEX({Reference Sheet Date Column}, MATCH(Helper@row, {Reference Sheet Helper Column}, 0))>= TODAY(365), "Red")

There could potentially be an issue depending on how the reference sheet is filled out. You may need to use this instead if there could be more than 1 entry with the most recent entry closer to the bottom of the sheet:

=IF(COUNTIFS({Date Column}, @cell >= TODAY(-365), {Helper Column}, @cell = SSO@row) > 0, "Red")

Reginald

After applying

=IF(INDEX({Reference Sheet Date Column}, MATCH(Helper@row, {Reference Sheet Helper Column}, 0))>= TODAY(365), "Red")

and

=IF(COUNTIFS({Date Column}, @cell >= TODAY(-365), {Helper Column}, @cell = SSO@row) > 0, "Red")

seperately. Its not returning in values.

What am i missing here?

Paul Newcome

Did you do the first part of inserting the helper column on both sheets so that all values are text values?

Ignore the above. I saw it after a closer look. Sorry about that.

Are you able to provide a screenshot of the Discipline sheet (just the dates) that shows some rows that should be getting counted?

Genevieve P.

Thanks for jumping in here to help, @Paul Newcome!

Paul Newcome

@Genevieve P. No worries. This one seems to have a few moving parts, so I figure an extra set of eyes couldn't hurt.

Reginald

Hey Paul sorry for the long delay. Yes i have inclued a sheet " Discipline". I did add some random information in the Name and the SSO columns. The 'Dates" column i have not change from the previous screen shot above.

Also, to compare to the Job intake form. I have included a screenshot from that one as well and cleaned up so that the informaton is the same on each sheet.

Currently the formula used on the " Job Application _ Intake Form" In the " Currently Has Discipline" Column

=INDEX({Discipline Tracker Master Range 1}, MATCH([Employee Id]@row,{Discipline Tracker Master Range 2}, 0)) >= TODAY(365), "Red")