I have created an attendance tracker. We recently implemented a point system. I need to create a formula that will allow me to subtract one point when the date of occurance has reached one year. Example: Employee calls in on 10/18/2018, acquires a point for the call in. The point will stay in total points until 10/18/2019, then total should drop by one.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,227
- Office Version
- 2013
- 2010
- Platform
- Windows
You've not given any indication of what your data looks like at all...
So, say you have 10/18/2018 in A1, dragging this down so Column A becomes your list of dates.
We'll use B1 to enter a X
And then in C366 (where A366 = 10/18/2019) you'll enter the formula =COUNTA(B1:B366)
This will count the number of non empty cells in Col B and give you the total amount of "Points"
Dragging down this formula will change it to constantly check the previous year and give you your rolling total, B1 will change to B2 and will no longer see the X in B1 removing it from the total.
Can you work with that?
So, say you have 10/18/2018 in A1, dragging this down so Column A becomes your list of dates.
We'll use B1 to enter a X
And then in C366 (where A366 = 10/18/2019) you'll enter the formula =COUNTA(B1:B366)
This will count the number of non empty cells in Col B and give you the total amount of "Points"
Dragging down this formula will change it to constantly check the previous year and give you your rolling total, B1 will change to B2 and will no longer see the X in B1 removing it from the total.
Can you work with that?
Upvote
0
Similar threads
- Question
