Conditional formatting or another option?

M

marisr

New Member
Joined
Feb 19, 2014
Messages
10
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
should be possible - depends on how you have the layout

you could test the date for 30 days and also use count for the number of occurances

for the date

depending on the date range

=cell the dates are in > datevalue( year(today()) , month(today()), day(today())-30 )
will look for dates greater than 30 days from today - rolling 30 days

then for count
depending on how you record occurrences

countif( range, criteria)>2

then we can use an AND to pull those together and use in conditional formatting

repeat for the other conditions
 
Upvote 0
etaf said:
should be possible - depends on how you have the layout

you could test the date for 30 days and also use count for the number of occurances

for the date

depending on the date range

=cell the dates are in > datevalue( year(today()) , month(today()), day(today())-30 )
will look for dates greater than 30 days from today - rolling 30 days

then for count
depending on how you record occurrences

countif( range, criteria)>2

then we can use an AND to pull those together and use in conditional formatting

repeat for the other conditions
Click to expand...

When I try the first section an error message pops up saying I have too many arguments. Right now the dates are only for a quarter (broken out by work days each week). Does that matter?
 
Upvote 0
should not

can you put the spreadsheet on a share, dropbox, skydrive - remember a public forum so no personal information

can you post the actual formula you are trying to enter - so we can decode for you
 
Upvote 0
Here is my formula =F9:HL9 > datevalue( year(today()) , month(today(), day(today())-30 )

I need it to be highlight anything less than 30 days so I assume I need to make this a < symbol. I don't have the ability from the work computer to put it on the cloud.
 
Upvote 0
in the conditional format you would do the following

for 2007 or 2010 excel version
Conditional Formatting


Highlight applicable range >>
F9:HL9


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=F9 > datevalue( year(today()) , month(today(), day(today())-30 )


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

this will now highlight all cells in the range F9 to HL9 that have dates greater than 30 days old

now we need to add the condition for your points system with an AND

so what are you counting - where are these ranges ???
 
Upvote 0
My work computer doesn't allow use of Drop Box but I am now back at my home office. I have never shared something via Drop box this way. It is asking me for an email address to share it with someone.
 
Upvote 0
You would need to setup an account for dropbox etc
if you have an outlook.com, hotmail.com - often now you would also by subscribed to skydrive
there are a few other share sites available
 
Upvote 0
I have a dropbox account but not able to access via the work internet connection. The document is out there but I just need to know who to share it with.
 
Upvote 0
post the link here - make sure no private data is in the spreadsheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional Formatting/Formula
2
Replies
12
Views
971
Joe4
Joe4
P
  • Question
Conditional formatting a cell that contains both numbers and letters
Replies
1
Views
396
pbailey
P
M
  • Solved
Using Conditional Formatting for Multiple Cells
Replies
3
Views
373
Fluff
Fluff
M
  • Question
Conditional Formatting and '=IF' help?
Replies
1
Views
382
awoohaw
awoohaw
D
  • Question
Highlight cell if date has passed
2 3
Replies
21
Views
1K
kevin9999
K

Forum statistics

Threads
1,214,851
Messages
6,121,931
Members
449,056
Latest member
denissimo

Share this page

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
Back
Top