Conditional formatting or another option?

[marisr]

I am working on an attendance spreadsheet where I want to be able to color code (conditional format) number of occurrences within a specific time frame. Right now I am calculating the total points and if it goes above a certain number then conditional formatting the cell. Here is what I want it to do and I don't know if Conditional formatting can be useful or if there is another formula to use:
1. If someone had more than 2 occurrences within a rolling 30 days I want the cell to highlight red.
2. If someone had one occurrences withing a rolling 30 days I want the cell to highlight yellow.
3. If someone had any occurrences within the first 14 days (work days - 5 days a week not 7) then it should highlight red.

Help!
Rachel

 

[marisr]

https://www.dropbox.com/s/52kw115hw11mlb8/Attendance%20Tracker%20Bacardi%20DC.xlsx</SPAN></SPAN>

 

[etaf]

if you could now use examples of what you want to test

I am working on an attendance spreadsheet where I want to be able to color code (conditional format) number of occurrences within a specific time frame. Right now I am calculating the total points and if it goes above a certain number then conditional formatting the cell. Here is what I want it to do and I don't know if Conditional formatting can be useful or if there is another formula to use:
1. If someone had more than 2 occurrences within a rolling 30 days I want the cell to highlight red.
2. If someone had one occurrences withing a rolling 30 days I want the cell to highlight yellow.
3. If someone had any occurrences within the first 14 days (work days - 5 days a week not 7) then it should highlight red

whats an occurrence - is that any of these codes?

RO	Pre-approved
CO	Called out
L	Leaving Early
NCN	No Call No Show

<tbody>
</tbody>

what other codes are enters - can you just count ? any item entered and use the date column

=COUNTIFS(F9:HQ9,">"&TODAY()-30,F11:HQ11,"<>")

that will count anything in the future - but you could add another condition for today
=COUNTIFS(F9:HQ9,">"&TODAY()-30,F9:HQ9,"<="&TODAY(), F11:HQ11,"<>")

 

[marisr]

if you could now use examples of what you want to test



whats an occurrence - is that any of these codes?

RO	Pre-approved
CO	Called out
L	Leaving Early
NCN	No Call No Show

<TBODY>
</TBODY>

what other codes are enters - can you just count ? any item entered and use the date column

I am confusing this for sure! The person can not have any points in the first 14 days and no more than 2 points in a rolling 30 day calendar. Thus why I have the start date listed and created a formula for number of days on assignment.

 

[etaf]

yes sorry, i'm lost now

how do you assign points and where are those entered

sorry about this , but i do need a full explanation, having the spreadsheet is now very useful - and we can certainly apply the colours - if i just knew the rules to apply

we can test for 14 and 30 days
using
today()-14
today()-30
and then test the dates

But what am i counting for points

 

[marisr]

yes sorry, i'm lost now

how do you assign points and where are those entered

sorry about this , but i do need a full explanation, having the spreadsheet is now very useful - and we can certainly apply the colours - if i just knew the rules to apply


we can test for 14 and 30 days
using
today()-14
today()-30
and then test the dates

But what am i counting for points

Each occurance has a point associated with it and the points are added together to have a total number of points. Any points within 14 days = termination; then after that 2 pts within a 30 day period = termination. I was thinking red for 2 pts, yellow for 1 - 1.5, and green for .5 or below.

 

[etaf]

sorry, still not getting this, how do i know what points are assigned to what occurrences ?
OR if each occurrence is worth 1 point then
how would they get 1.5 or .5 points - if each occurrence is worth a point ?

 

[marisr]

there are drop down menus so that we pick say "L" for late and put that in the date columns. The spreadsheet is counting where we have a data point to capture. then I have a formula calculating the points based on occurances. one point for a Call Out, 1/2 point for a late or leave early.

 

[etaf]

where in the spreadsheet are you counting and applying the points
I'm finding this quite difficult now to actually understand what you want and how you are using the spreadsheet
first we had :-

1. If someone had more than 2 occurrences within a rolling 30 days I want the cell to highlight red.
2. If someone had one occurrences withing a rolling 30 days I want the cell to highlight yellow.
3. If someone had any occurrences within the first 14 days (work days - 5 days a week not 7) then it should highlight red.

Now that HAS now changed to

Each occurance has a point associated with it and the points are added together to have a total number of points. Any points within 14 days = termination; then after that 2 pts within a 30 day period = termination. I was thinking red for 2 pts, yellow for 1 - 1.5, and green for .5 or below.

And I still dont know where you are applying the points - I'm quite keen to help people on the forum, but I think this i'm not following you at all now.

For me to help out any further, I need a full explanation of where you are recording the points on the spreadsheet - so i can help you apply a conditional format - otherwise I suspect another member will need to help out, as i'm really missing what to do and how to help you out , as seen by the length of the post now

 

[marisr]

The ultimate goal of the spreadsheet is for my managers to be able to quickly track, after recording any attendance occurances, if someone is approaching the max points before their staff are to be terminated. There is a line for each associate and then when they call out, leave early, no call no show (NCNS), then the managers log a "CO" OR "LE" or "NCNS" via a drop down list under the specific day of the occurance. Each occurance has a point value. The occurances are tallied and then the appropriate total point value is assigned (some occurances have a 1/2 point and others have a full point value). Where the complexity comes in to play is that there are 2 time bound thresholds. If you have any occurances within the first 14 days of your work assignment you are terminated. After that we track the occurances on a rolling 30 day calendar. You can not have more than 2 POINTS within a 30 day calendar. I typically use a stop light color coding system and that is where I thought the conditional formatting could work here.

If there is any occurance within 14 days of the start date then it should be color coded red;
after that date it would like this: Highlight the cell GREEN if the point value is 0-.5; Highlight the cell Yellow if the point value is 1.0-1.5; Highlight the cell if the point value is greater than 2 points.

Again its a rolling 30 day calendar.

Does that help?

 

[etaf]

A little
you still havent explained where the points are and what points are assigned to what occurances - so ihave had to look throughout the spreadsheet and try and work it out
so this maybe wrong

you say

log a "CO" OR "LE" or "NCNS"

BUT in the sample spreadsheet you have
log a "CO" OR "L" or "NCNS"
and NOT "LE"

looking at you formulas - its only "L" that has a value of 0.5 and not 1

So in a new column HX- you can add up the points for the last 30 days using the following formula
=COUNTIFS($F$9:$HQ$9,">"&TODAY()-30,$F$9:$HQ$9,"<="&TODAY(), F11:HQ11,"RO")+COUNTIFS($F$9:$HQ$9,">"&TODAY()-30,$F$9:$HQ$9,"<="&TODAY(), F11:HQ11,"CO")+COUNTIFS($F$9:$HQ$9,">"&TODAY()-30,$F$9:$HQ$9,"<="&TODAY(), F11:HQ11,"L")/2+COUNTIFS($F$9:$HQ$9,">"&TODAY()-30,$F$9:$HQ$9,"<="&TODAY(), F11:HQ11,"NCNS")

now you can use that column to apply conditional formatting -
not sure what cell you want to apply that to - so i have chosen to use the NAME column

in column HY I have put
for 14days from startdate use
=COUNTIFS($F$9:$HQ$9,"<"&E11+14,$F$9:$HQ$9,">="&E11, F11:HQ11,"<>")

NOW you can apply conditional formatting
2 rules

BUT your highlight values

after that date it would like this: Highlight the cell GREEN if the point value is 0-.5; Highlight the cell Yellow if the point value is 1.0-1.5; Highlight the cell if the point value is greater than 2 points.

what happens between 0.5 and 1
what happens between 1.5 and 2

anyway
so RULE 1 for RED would be
formula would be
=OR($HX11>=2,$HY11>0)
and apply to the cells you want highlighted
in this case B11:B229

2nd rule
for green
= $HX11<=0.5

for yellow
$HX11<=1

make sure you put in the correct order and use stop if true
so 0.5 first
then 1
then 1.5
then 2

that way , the formula does not have to test between values - but you could use that

does this help at all ?

https://www.dropbox.com/s/ikh3emitg48q0xf/Attendance Tracker Bacardi DC_etaf_1.xlsx