Identifying dates as numbers for a formula

palhow23

New Member
Joined
Mar 8, 2016
Messages
4
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
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
Joined
Mar 8, 2016
Messages
4
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

Active Member
Joined
Nov 12, 2015
Messages
263
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
Joined
Nov 12, 2015
Messages
263

ADVERTISEMENT

Did you have any luck with this one?
 

palhow23

New Member
Joined
Mar 8, 2016
Messages
4
MisterProzilla, worked a treat. Thank you so much. I appreciate your time and effort more than i can convey.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top