Chris_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 188
- Office Version
- 365
- Platform
- Windows
Hello,
I have an absence tracker which records absence in a current 6-month rolling period and a previous 6-month rolling period.
Each occasion of absence is recorded as a separate row and absence is manually added to the "current 6 month" sheet daily (as it occurs). A piece of VBA code then looks at the absence start date and if it falls before 6 months from the current day, it would automatically move the whole row to the next blank row on the "previous 6 months" sheet. It also looks at the "previous 6 months" sheet and any rows with an absence start date less than 12 months from today will go to the "previous 12-24 months" sheet and will then be automatically deleted from 12-24 months, if more than 24 months old.
Each occasion of absence is recorded on its own row, with the unique employee ID in column A (which uses the REPT formula to get leading 0's in employee ID from column I). So, if an employee was sick on 05/05/2022 for 1 day and then sick again on 06/06/2022, the employee would have 2 rows. Example:
I need a separate sheet to tell me how many occasions of absence each employee has over the past 12 months (i.e. over the two sheets "current 6 months" and "previous 6 months"), so an "Occasions last 12 months" sheet.
As per picture above, I did this for looking at only current 6 months using =Unique(Filter(XXXX,XXXX<>"")) on employee ID to pull a list of unique values, then used COUNTIF to count how many times the employee ID appeared in "current 6 months", which looks like this:
BUT this method won't work when the data is split over 2 sheets. Not sure if this can be formula driven or needs VB Code.
Kind Regards
I have an absence tracker which records absence in a current 6-month rolling period and a previous 6-month rolling period.
Each occasion of absence is recorded as a separate row and absence is manually added to the "current 6 month" sheet daily (as it occurs). A piece of VBA code then looks at the absence start date and if it falls before 6 months from the current day, it would automatically move the whole row to the next blank row on the "previous 6 months" sheet. It also looks at the "previous 6 months" sheet and any rows with an absence start date less than 12 months from today will go to the "previous 12-24 months" sheet and will then be automatically deleted from 12-24 months, if more than 24 months old.
Each occasion of absence is recorded on its own row, with the unique employee ID in column A (which uses the REPT formula to get leading 0's in employee ID from column I). So, if an employee was sick on 05/05/2022 for 1 day and then sick again on 06/06/2022, the employee would have 2 rows. Example:
I need a separate sheet to tell me how many occasions of absence each employee has over the past 12 months (i.e. over the two sheets "current 6 months" and "previous 6 months"), so an "Occasions last 12 months" sheet.
As per picture above, I did this for looking at only current 6 months using =Unique(Filter(XXXX,XXXX<>"")) on employee ID to pull a list of unique values, then used COUNTIF to count how many times the employee ID appeared in "current 6 months", which looks like this:
BUT this method won't work when the data is split over 2 sheets. Not sure if this can be formula driven or needs VB Code.
Kind Regards
Last edited:
