+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Cool 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 ashburnadam@gmail.com should you have any question or suggestion.
    You can also reply this post / thread.
    Looking forward to an idea.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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()))
    Last edited by daddylonglegs; 08-19-2012 at 01:29 PM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    08-18-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Red face Re: Determine last date of attendance as we considered weekend, public holiday and absent

    Hi daddylonglegs,

    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.

    Ashburnadam
    Last edited by Cutter; 08-21-2012 at 07:43 AM. Reason: Removed whole post quote

  4. #4
    Registered User
    Join Date
    08-18-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2013
    Posts
    18

    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.
    Last edited by Cutter; 08-21-2012 at 07:42 AM. Reason: Removed whole post quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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