Formula to subtract attendence points after 30 days, but only to one instance at a time.

seanjon

New Member
Joined
Dec 23, 2017
Messages
13
Good Day!

The company allows for 5 attendence points to be removed after 30 days of no instances. I have a tracker that does that, but unfortunately, I didn't account for it to apply it to all the cells that carry points. For example, 5 points were assigned 45 days ago, another 5 40 days ago and 3 points 35 days ago. After 30 days of no instances, the 5 points from 45 days ago should be adjusted to zero, which the formula did, however, it also removed the points from 40 and 35 days ago. How do I make it so it only does it once every 30 days?
A = Employee, B= Start date, C = Stop Date, D = Type of absence, E= leave used, F=Points assigned, G=Notes, H=adjusted points

F should not change at all. Once it is input, this will stay. F is where I need to put the formula in do that it removes 5 points After 30 days only if there is no other instance from today - 30 days. Hoever, it needs to do it only once per 30 days. This is the formula I have at this time: =IF(AND(A3<>"", TODAY() - MAXIFS(C:C, A:A, A3) >= 30), MAX(0, F3 - 5), F3)

Thanks for your time and support.
 

Attachments

  • attend.JPG
    attend.JPG
    103.8 KB · Views: 2

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,223,606
Messages
6,173,323
Members
452,510
Latest member
RCan29

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