Identifying dates as numbers for a formula

[palhow23]

HI Guys,

I am trying to set up a training matrix using excel.
I am trying to conditionally format a cell so it recognizes when a date is entered (Training completed) and format a tracking cell so when 5 training events have been completed it is highlighted green and shows "Level 1" .

Once 5-10 training events completed shows red and Level 2. etc etc

I have a COUNTIF in place and have the client input a number instead of a date however clients likes to see the date in the matrix rather than a number 1.

Any help would be greatly appreciated

palhow23

 

[MisterProzilla]

For the date cells, you could use the 'Format Only Cells that Contain' - 'No Blanks' Conditional format. That'll colour any cell that contains any data, dates included.

For the tracker cell, try something like this formula, with each level nested as an IF, starting with the highest level:


IF(COUNTA(A2:G2)>=10,"Level 2",IF(COUNTA(A2:G2)>=5,"Level 1",""))


COUNTA counts any non-blank cells. So first it's checking to see if Level 2 has been achieved with a count greater than or equal to 10 non-blank cells in A2:G2 (or whatever you range is) & returns 'Level 2' if true; if not, it checks for the next level down, etc. If you've got any levels above 2 you'd add them like this:

IF(COUNTA(A2:G2)>=15,"Level 3",IF(COUNTA(A2:G2)>=10,"Level 2",IF(COUNTA(A2:G2)>=5,"Level 1","")))

Then for the Conditional Formatting of the tracker cell, you'll want a separate rule for each possible level: each using the 'Format Only Cells that Contain' - 'Specific Text'. Type the level in the specific text field and colour it as you like.

See if all that works.

 

[palhow23]

Hi MisterProzilla,

I tried applying the formulas but do not have any luck, the text does not show up in the tracker cell??
I did have luck using 1 rule and colour change but still no text??

Any ideas

Thanks in advance

 

[MisterProzilla]

Hmm, I've set up a test an the formula seems fine, it might be that I've assumed the wrong layout ...

What's the range where you would be entering dates for completed training? Have you got different training sessions named along the top, staff down the left-hand side, and you just enter a date when they've completed a particular session/course?
Where are the tracker cells?

Which formatting rule worked?


You could try re-creating my test sheet. In a blank excel file, try following this set-up to the letter and see if it works:

I've got a bunch of training sessions named from B1 to U1, and staff names down the left in column A. In V1 I've got 'Level' and in V2 I've entered the formula below and autofilled down to V100 (ie the table is now big enough for 99 staff records):
IF(COUNTA($B2:$U2)>=15,"Level 3",IF(COUNTA($B2:$U2)>=10,"Level 2",IF(COUNTA($B2:$U2)>=5,"Level 1","")))

I've set up the following Conditional Formats:
In range B2:U100: 'Format Only Cells That Contain' - 'No Blanks', coloured light green
In range V2:V100: 'Format Only Cells That Contain' - 'Specific Text' - 'containing' - "Level 1", coloured green
In range V2:V100: 'Format Only Cells That Contain' - 'Specific Text' - 'containing' - "Level 2", coloured red
In range V2:V100: 'Format Only Cells That Contain' - 'Specific Text' - 'containing' - "Level 3", coloured orange

Once that's set up, enter a few dates in the grid and see what you get

 

[MisterProzilla]

Did you have any luck with this one?

 

[palhow23]

Hi MisterProzilla just trying out now. Will let you know

 

[palhow23]

MisterProzilla, worked a treat. Thank you so much. I appreciate your time and effort more than i can convey.

 

[MisterProzilla]

No problem, glad you got it working