+ Reply to Thread
Results 1 to 7 of 7

Attendance Tracker

  1. #1
    Registered User
    Join Date
    02-11-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    14

    Attendance Tracker

    Hi everyone,

    I have been tasked with coming up with an attendance tracker at work. I have been messing with it for a while now and I have been surfing the web for answers. I have found a lot of things that are similar, but I can't quite adapt them to what I want to do.

    So, we award 1/3 of a point for a tardy and 1 point for an absence.

    The Tardy/Absence criteria will pull from another spreadsheet.

    I used a countif to determine the number of points based on Tardy/Absent.

    I am now trying to create a rolling 90 day total and a rolling 365 day total. So I will pull up the file, add a new column that will pull the previous day's Absences/Tardies and then have my 90/365 day period update to reflect the new date.

    I have been messing around with OFFSET and EDATE, which are new to me. If anyone can help me come up with a formula to figure this out, I would be ecstatic! It would be even better if you could explain how it was done as well so I can learn. Thanks!

    ~B
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Attendance Tracker

    Using your sample:

    Please Login or Register  to view this content.

    you could use OFFSET but unlike INDEX construct above it's Volatile so I prefer the INDEX route myself... up to you of course...

  3. #3
    Registered User
    Join Date
    02-11-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Attendance Tracker

    When I add in a new column, what changes will I need to make to the forumla? I assume moving the COLUMN reference one column over? I haven't used that function before.

    The more I learn about Excel, the less I feel I know...

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Attendance Tracker

    The function is designed such that you won't have to change anything... assuming you're always inserting the new column before the calc columns of course

    (it works on the assumption that the most recent data is always the column immediately to the left of the calculation columns themselves)

    If you want to test - change 90 to a much smaller value - say 5 and begin inserting columns - that should help you validate that it's working to your specification(s).

    Post back and let us know either way.

  5. #5
    Registered User
    Join Date
    02-11-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Attendance Tracker

    Ok, I got it. It's working fine, Thanks!

  6. #6
    Registered User
    Join Date
    10-06-2015
    Location
    Fort Worth, TX
    MS-Off Ver
    2013
    Posts
    1

    Re: Attendance Tracker

    I'm very very new at Excel but and this formula is excellent but how can I have this formula calculate Absent =2 Tardy = 1 and Early Out = 1? Thanks for any help anyone can provide.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,979

    Re: Attendance Tracker

    overa32182,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ 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