janarthenan
New Member
- Joined
- Jun 11, 2016
- Messages
- 22
Hi Peter
Hopefully I am not being a pain and was wondering if you are able to help me with something else please.
I have created a training matrix and I need to calculate some percentages on a weekly basis.
The problem I am facing is that - last week my percentage was lets say 15, but when i enter a new date and get 20% this week, this also changes it for the last week.
Is there at all a way for the percentage to ignore values that are of a future date?
<tbody>
</tbody>
This is what I am using currently:
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
But the problem with this is If someone completes a training on the 30th of July, this changes the percentage for Week Ending 15th and the 29th of July.
So my objective is: If a staff has completed a training and he is NOT a leaver, and his start date is BEFORE the week ending date and the date of his training is LESS than a year (we do our training again every year) and if a new training date is entered this should not affect the previous week.
Thank You!
J
Hopefully I am not being a pain and was wondering if you are able to help me with something else please.
I have created a training matrix and I need to calculate some percentages on a weekly basis.
The problem I am facing is that - last week my percentage was lets say 15, but when i enter a new date and get 20% this week, this also changes it for the last week.
Is there at all a way for the percentage to ignore values that are of a future date?
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Column1 | Start Date | Designation | Infection control | Fire Safety and Awareness | Health and Safety | Leaver |
2 | 1 | 05/03/2018 | HSCW | leaver | |||
3 | 2 | 05/02/2013 | SN | 28/08/2017 | 28/08/2017 | 28/08/2017 | |
4 | 3 | 01/08/2013 | HSCW | 26/09/2017 | 26/09/2017 | 26/09/2017 | |
5 | 4 | 26/09/2014 | SN | 13/11/2017 | 13/11/2017 | 13/11/2017 | |
6 | 5 | 19/04/2017 | HSCW | 24/05/2017 | 01/05/2017 | 24/05/2017 | leaver |
7 | 6 | 15/09/2017 | Admin | 23/01/2018 | 25/01/2018 | 23/01/2018 | |
8 | 7 | 13/09/2017 | HSCW | 14/09/2017 | 14/09/2017 | 14/09/2017 | |
9 | 8 | 22/10/2014 | OT | 14/09/2017 | 08/09/2017 | 08/09/2017 | |
10 | 9 | 17/11/2014 | HSCW | 23/08/2017 | 23/08/2017 | 24/08/2017 | |
11 | 10 | 07/08/2017 | MANAGER | 05/09/2017 | 05/09/2017 | 05/09/2017 | |
12 | 11 | 04/12/2017 | D.MANAGER | 05/12/2017 | 05/12/2017 | 05/12/2017 | |
13 | 12 | 04/11/2015 | HSCW | 11/09/2017 | 21/09/2017 | 21/09/2017 | leaver |
14 | 13 | 08/04/2016 | HSCW |
<tbody>
</tbody>
This is what I am using currently:
C | D | E | F | |
---|---|---|---|---|
40 | Week Ending | Infection control | Fire | Safeguarding Adults |
41 | 15-Jul-18 | 93% | 93% | 93% |
42 | 29-Jul-18 | 93% | 93% | 93% |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Stat. & Mand. Training
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>
But the problem with this is If someone completes a training on the 30th of July, this changes the percentage for Week Ending 15th and the 29th of July.
So my objective is: If a staff has completed a training and he is NOT a leaver, and his start date is BEFORE the week ending date and the date of his training is LESS than a year (we do our training again every year) and if a new training date is entered this should not affect the previous week.
Thank You!
J