Determine last date of attendance as we considered weekend, public holiday and absent date

Hi there...

My name is ashburnadam and I'm from Malaya (now Malaysia).

Please refer to the attachment.
2K12SMM3 LEVEL 1.xlsx

There are 3 Worksheets:

1. Worksheet Calendar --> Used to refer off days including semester break, additional holiday, public holiday, it's replacement if the holiday falls on Sunday (if both Sunday and Monday is Public Holiday, the replacement will falls on subsequent Tuesday).
2. Worksheet Statistics --> Used to refer the summary of attendance performance.
3. Worksheet Data --> Used to store the data of absent date for particular student.

My concepts are as follows:

• The worksheet only can store 40 students (which is maximum number of students in a class).
• As a teacher, I just have to key in the date of absent for particular students in Worksheet Data.
• Based on the absent dates, the spreadsheet will calculate the attendance performance for each student.
• We also can determine when is the last date of absent and latest date of attendance.
• Safe time by key in dates of absent only, not key in both attendance and absent dates since we already know the period of study.

Statistics!D:E --> Join date and end date (end date of a semester).
Statistics!F:G --> Overall and up to date number of study days for each student calculated since start date.
Statistics!H:I --> Number of current attended days and absent days calculated.
Statistics!J:K --> Up to date and overall attendance performance (in percentage) for each student.
Statistics!L:L --> Latest date of absent (extract from Worksheet Data using MAX function).
Statistics!M:M --> HERE IS MY PROBLEM, I really need to determine the last date of attendance for each particular student.

The conditions taken into account just to determine last date of attendance are:

• Automatic calculated
• It is not within absent dates as per found in Worksheet Data
• If TODAY is after or at the end date of study, last date of attendance will appear to be end date of study
• The last date of attendance is not in off days, nor public holiday, nor semester break nor additional holiday
• Last date of attendance is not on weekend
• If all conditions 2-5 above fail, the last date of attendance is today.

So, I hope anyone can help me on the matter of how we can determine the last date of attendance using only absent date information.

Best regards...

Ashburnadam

P/S: Please e-mail me at [email protected] should you have any question or suggestion.
You can also reply this post / thread.
Looking forward to an idea.