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

1. ## 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).

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...

P/S: Please e-mail me at ashburnadam@gmail.com should you have any question or suggestion.
Looking forward to an idea.

2. ## Re: Determine last date of attendance as we considered weekend, public holiday and absent

Try this formula in M4 copied down

=IF(TODAY()>=E4,E4,IFERROR(WORKDAY(TODAY(),-1,IF({1,0},Calendar!A\$3:A\$100,INDEX(Data!A\$3:AN\$100,0,MATCH(B4,Data!A\$2:AN\$2,0)))),TODAY()))

3. ## Re: Determine last date of attendance as we considered weekend, public holiday and absent

Thank you for your effort in helping us out.

Refer to the attachment, it is the modified version of the original document, but the concept is still the same.
2K12SMM3 LEVEL 1 (V2.0).xlsx

Just a single puzzle I have in my mind right now, based on the solution you give, can it determine last date of attendance given if a student already absent for three straight days?

I hope you can reply me...

Thank you so much, indescribable by words.

4. ## Re: Determine last date of attendance as we considered weekend, public holiday and absent

Oh... I'm sorry.
I already checked it, it works even if the student has absent for more than 2 days.

Problem's solved.

Thank you.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1