# Identifying dates as numbers for a formula

#### palhow23

##### New Member
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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### MisterProzilla

##### Active Member
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

##### New Member
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

#### MisterProzilla

##### Active Member
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

##### Active Member

Did you have any luck with this one?

#### palhow23

##### New Member
Hi MisterProzilla just trying out now. Will let you know

#### palhow23

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

#### MisterProzilla

##### Active Member
No problem, glad you got it working

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,651
Messages
5,854,959
Members
431,689
Latest member
jacker01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back