Complex(?) Conditional Formatting Query

[mcfrenzy96]

Hello All

I have a query about a possible complex set of conditional formatting formulae. At work, my sales team are targetted on certain activites which generate them 'points' These points go on to affect their monthly ratings and overall bonuses. Depending on the type of activity they complete the points will be different. So far so easy. The crucial point is that depending on the acitivity, the points will apply on different dates. For example some items will add points the next day, others at the end of the week and others are awarded X number of days after the activity.

I want everyone to keep track of their points to make sure they are being awarded what they deserve but at the moment, they are just adding things up as they go. The spreadsheets look something like this:

spreadsheet.jpg

What I am trying to use conditional formatting for is so that if they add the forward date the points will be applied on that day. In my mind the conditional formatting id something along the lines of:

If 'date points applied' is equal to or less than 'TODAY()' then 'points added to total'

Using the screenshot above it would be along the lines of:

If 'C4' equal to or less than 'TODAY()' then 'B4' is added to the sum for 'B7'

Any ideas or solutions?

[p24leclerc]

I think you should be good with a formula like this one:
It adds only points where the date they apply is less than or equal to NOW().

Please Login or Register  to view this content.

see attached file