Reference current date and compare it to multiple cells

Hello,

My experience with excel is very limited and I somehow got stuck creating a complex workbook for my company . I've done pretty well so far but I have run into an issue I cannot figure out or been able to find on the web. I'm hoping somone here can help. Here's what I'm trying to do......

I'm tracking training for my company. We have 3 sets of training. Training that needs to be completed within 5 days, 30 days and 6 months of an employee start date. I have columns that calculate the due date based on an employee start date and the status of each, whether it's complete or not complete, but I have another column for each that I want to track if we're in compliancy.

For example, if it's the employee's 3rd day at work and he hasn't completed all the training within 5 days, we're still in compliancy, even though the status column will say Not Complete. I need a formula that will look at the current date, compare it to the start date as well as look at the status of each training and tell me if that employee is stil in compliancy.

I hope it's not as confusing as it sounds! Thanks in advance to anyone who can help. I really appreciate it.

That sounds do-able.

Can you give a little more detail on which data you have in each column, with examples and desired results, a small sample spreadsheet might help, you can attach it to your post

When uploading your attachment please also be sure to modify your thread title to something more appropriate (per Forum Rules)

Originally Posted by Cutter

DLL

Are you getting soft on Rule #1?

I've been concentrating on Rule 14...........

Firstly, why do you have different date formats?
Col D is formatted as: yy-mm-dd
Col F is formatted as: mm-dd-yy


As far as your L col is concerned, try this:

=IF($D3="","",IF(OR(AND(I3="Complete",F3-$D3<6),TODAY()-$D3<6),"Yes","No"))

Adjust for cols M & N so that in M3 use:

=IF($D3="","",IF(OR(AND(J3="Complete",G3-$D3<31),TODAY()-$D3<31),"Yes","No"))

and in N3 use:

=IF($D3="","",IF(OR(AND(K3="Complete",H3-$D3<182),TODAY()-$D3<182),"Yes","No"))

Not sure about that 182 but since you were using 181 in col H that's what I went with.

These formulas result in the same Yes No results you already had in cols L:M

The one I gave does a check on whether or not the "Complete" complies with the limitation.

Yours will give a "Yes" result merely because it says "Complete". So, for instance, if the 5 day training was completed 7 days after start date you will get a "Yes" with yours and a "No" with mine.

But you should replace the TODAY()-$D3<6 in mine with your simplification: TODAY()<=F3

Note the <= instead of just <

Then you shouldn't use the word "compliant" - just "done".

And doesn't that make those columns redundant? You have 3 columns giving a "Complete" or "Incomplete" on whether or not the training was done. Now you are just saying "Yes" or "No" in the next 3 columns on whether or not the training was done>