Rolling Calendar Attendance and Tardy Tracker

[rnawallin]

I'm trying to help my manager create a spreadsheet to track various types of absences and leave. I've linked the spreadsheet here to see what I have set up, but need to figure out how to extract the right data. I am looking for a way to figure out absences based on a rolling calendar year, and make two tardies in a two week period come up as an absence. Is any of this possible? Thanks!


https://1drv.ms/x/s!Ao6sCDwN2UE6pWpVwICs0-lThWlT?e=qZMCMX

Anna

 

[MARK858]

Please define exactly what you mean by a rolling year. To me where we use a rolling year it is from todays date back exactly one year and any sick days in that period.
The reason that I am asking is in your spreadsheet you only have January and February 2020, which if you were using the same definition wouldn't make any sense.

 

[rnawallin]

Yes, it is what you are referring to - today's date back one year and anything in that period. We are working on setting up the spreadsheet from scratch, so we haven't gone in and entered all the back dated info. We wanted to figure out if it is even possible to do the rolling calendar year before spending the time entering all the previous sick calls.

 

[MARK858]

Rolling year is ok to do as long as where you have the days can be a real date formatted for the day. I can't do it until Saturday now because of other commitments but if you confirm that they can be real dates I'll look at it then if no-one else has.
The other part of your question is harder.

 

[rnawallin]

Thank you so much for the reply. After a lot of searching through message boards this morning, I found a formula that I was able to adapt for the rolling calendar year absences. That one is resolved at this point.

The other part of the question, if it's possible to make two tardies in a two week period come up as an absence, that I am still struggling with. It may not be possible to do, at least not in the way I have the spreadsheet formatted. Would something like that be easier to do if we tracked the tardies in a separate spreadsheet? Thank you!

 

[Roger Govier]

Hi

I have uploaded a copy of your file here
Absence Calendar
In this file, I changed the year to 2019 to be able to test.
Row 3 contains all calendar dates formatted as just "dd" to show the day number only.
In cell B93 I entered the End date. In use you could use =TODAY() to have it automatically change to the latest date.
In cell D94 I entered the number of rolling months you want. In you case this would be 12, but for testing I entered 6
In cell B94 I entered a formula to calculate the date which is the selected number of months prior to the Actual date with the formula
=DATE(YEAR(B93),MONTH(B93)-D94,DAY(B93)+1)

Now, we need to calculate the variable Data range we want to use.
I created some named ranges

Actday	=MATCH(Sheet1!$B$93,Sheet1!$3:$3,0)
Firstdate	=MATCH(Sheet1!$B$94,Sheet1!$3:$3,0)
Data	=EVALUATE(ADDRESS(4,Enddate)&":"&ADDRESS(87,Actday))

The first 2 calculate the ending column and starting column to use by matching the dates in cells B93 and B94 with the date in row 3.
Data uses a very old XL4 Macro that can only be used in a named formula, to convert the addresses to a range. Using thsi the file has to bee saved as a.xlsm or a .xlsb file. Evaluate is extremely fast and is non volatile which is why I like it.

If for any reason you don't want to use this, then you could instead use the INDIRECT function (which is volatile, and I avoid all volatile functions where possible

Data	=INDIRECT(ADDRESS(4,Enddate)&":"&ADDRESS(87,Actday))

The formulae for your answers in cells B97:B104 then become simply
=COUNTIF(Data,C97) and copied down.

I can't think of a way just now to answer your second question,but if I get a chance to look at it tomorrow and come uo with a solution, I will post again.