Hi, I'm creating a system to track course completion data, which assigns a status to each row entry.
I'm trying to workout how to automatically assign 1 of 4 possible 'status' (archived, compliant, final month, or overdue) to each row based on today's date, the completion date, and the due date. In addition, the status needs to change to Archived when another row is entered by the same person with the same course title, but a different completion date. I've pasted an example below and for the purpose of the example, I'm using todays date as 22 June 22.
To explain further,
The Status of an entry (row) will change depending on today’s date (Variable), and the Date Completed (Fixed – from MS Form) and Date Due (Fixed due to course freq).
NB. When the second entry is made and results in a ‘compliant’ status, the first entry needs to change to ‘Archived’.
My formula in column I is:
=IF(TODAY() <= (EDATE($H2,-1)), Validation!$C$4, IF( AND( TODAY() > (EDATE($H2,-1)), TODAY()<=($H2-1)), Validation!$C$3, IF(XLOOKUP(1, (E:E=$E2) * (C:C=$C2), D:D,,, -1) = $D2, Validation!$C$2, Validation!$C$5)))
Please note Validation tab, column C holds the text for the 4 status', e.g., C2= Overdue, c3= Final Month, c4 = Compliant, c5 = Archived.
The formula works in most circumstances. However, there are two problems:
1. Dates of learning achieved must be entered in chronological order. Where a 'compliant' date is entered prior to an 'Archived' date, the system will recognise the Archived Date out of these two records and give it a Status of OVERDUE!!!
2. Two entries by the same person for the same course: entry 1 completion date = 25 Jan 22, entry 2 completion date = 20 Jun 22, results in both entries being assigned 'compliant' status. This is because of the first part of the formula doesn't account for this. What should happen is that when a 2nd entry is submitted by the same person for the same course, but with a different date, the older entry is assigned the status of 'Archived' and the new entry is assigned 'compliant' - assuming that it is within the (see due date).
I appreciate that this is a complicated system, and I'm open to all suggestions of help and advice.
Many thanks
I'm trying to workout how to automatically assign 1 of 4 possible 'status' (archived, compliant, final month, or overdue) to each row based on today's date, the completion date, and the due date. In addition, the status needs to change to Archived when another row is entered by the same person with the same course title, but a different completion date. I've pasted an example below and for the purpose of the example, I'm using todays date as 22 June 22.
To explain further,
The Status of an entry (row) will change depending on today’s date (Variable), and the Date Completed (Fixed – from MS Form) and Date Due (Fixed due to course freq).
NB. When the second entry is made and results in a ‘compliant’ status, the first entry needs to change to ‘Archived’.
My formula in column I is:
=IF(TODAY() <= (EDATE($H2,-1)), Validation!$C$4, IF( AND( TODAY() > (EDATE($H2,-1)), TODAY()<=($H2-1)), Validation!$C$3, IF(XLOOKUP(1, (E:E=$E2) * (C:C=$C2), D:D,,, -1) = $D2, Validation!$C$2, Validation!$C$5)))
Please note Validation tab, column C holds the text for the 4 status', e.g., C2= Overdue, c3= Final Month, c4 = Compliant, c5 = Archived.
The formula works in most circumstances. However, there are two problems:
1. Dates of learning achieved must be entered in chronological order. Where a 'compliant' date is entered prior to an 'Archived' date, the system will recognise the Archived Date out of these two records and give it a Status of OVERDUE!!!
2. Two entries by the same person for the same course: entry 1 completion date = 25 Jan 22, entry 2 completion date = 20 Jun 22, results in both entries being assigned 'compliant' status. This is because of the first part of the formula doesn't account for this. What should happen is that when a 2nd entry is submitted by the same person for the same course, but with a different date, the older entry is assigned the status of 'Archived' and the new entry is assigned 'compliant' - assuming that it is within the (see due date).
I appreciate that this is a complicated system, and I'm open to all suggestions of help and advice.
Many thanks
