Formula or VBA to track pro-rated attendance hours

[bh24524]

Hi, I'm looking for some help with a scenario regarding employees who have completed a probationary period of employment. When this happens, they fall under the company attendance policy for unexcused absences or late arrivals. In a normal circumstance, someone would be entitled to 104 hours that they can use between all types of unexcused absences, late arrivals, leaving early or whatever the case may be where they end up not working their full shift. This is a process that resets every March where hours are erased and people have the full 104 allotment. If however someone completes their probation period AFTER March, they earn pro-rated hours the first day of every month until the following March. That pro-ration schedule is as follows:

I have a spreadsheet that lists specific dates that employees completed probation. These dates are in column J. Ideally, I'd like to have another column that automatically updates the hour values on the first of every month and that could be in column I. Whether this would be better done by VBA or by some kind of formula, I'm not sure.

Here are a couple examples. Let's say we have one person completing probation in June(Person 1) and another completing in July(Person 2).

EXAMPLE 1
Person 1, I'd expect in June to see column I auto-populate 8.8 hours, July 1 would populate 17.6 hours(8.8+8.8), and August 1 would populate 26.4 hours (8.8+8.8+8.8)

EXAMPLE 2
Person 2 I'd expect in July to see column I auto-populate 8.7 hours and then on August 1, they would get 17.4 hours (8.7+8.7), so I would auto-update to 17.4.

Could something also be somehow incorporated that either the cell that is updating the value every month lights up a different color maybe the first few days of every month or maybe even a pop-up message upon the workbook opening at that time alerts that a value has been updated?

Note it doesn't matter when in the month someone completes their probations, if it is completed in that month, they get the allotment for that month.

 

[bh24524]

Sorry, I received new info because I was confused on what was provided to me in the screenshot I sent. It should only look like this:

That additional column was the OLD policy and meant to be a before/after pic of policies, so it has no relevance here.

 

[felixstraube]

Is this what you need?:

For testing I added some "current date" columns (K:S):

Freq.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1										Probation completion date	01/06/2024	01/07/2024	01/08/2024	01/09/2024	01/10/2024	01/11/2024	01/12/2024	01/01/2025	01/02/2025
2										Jun-24	8.8	17.6	26.4	35.2	44	52.8	61.6	70.4	79.2		Apr	8.6
3										Jul-24		8.7	17.4	26.1	34.8	43.5	52.2	60.9	69.6		May	8.7
4										Aug-24			8.8	17.6	26.4	35.2	44	52.8	61.6		Jun	8.8
5										Sep-24				8.8	17.6	26.4	35.2	44	52.8		Jul	8.7
6										Oct-24					9	18	27	36	45		Aug	8.8
7										Nov-24						9	18	27	36		Sep	8.8
8										Dec-24							9	18	27		Oct	9.0
9										Jan-25								5	10		Nov	9.0
10										Feb-25									5		Dec	9.0
11																					Jan	5.0
12																					Feb	5.0
Sheet4

Cell Formulas
Range	Formula
K2:S10	K2	=LET(h, $V$2:$V$12, s, SEQUENCE(11), start_i, MATCH(MONTH($J2), MONTH($U$2:$U$12), 0), end_i, MATCH(MONTH(K$1), MONTH($U$2:$U$12), 0), f, FILTER(h, s=start_i), n, end_i-start_i+1, r, f*n, IF(r<=0, "", r) )

your final formula would be this:

Freq.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1									Hours	Probation completion date
2									17.6	Jun-24											Apr	8.6
3									8.7	Jul-24											May	8.7
4										Aug-24											Jun	8.8
5										Sep-24											Jul	8.7
6										Oct-24											Aug	8.8
7										Nov-24											Sep	8.8
8										Dec-24											Oct	9.0
9										Jan-25											Nov	9.0
10										Feb-25											Dec	9.0
11																					Jan	5.0
12																					Feb	5.0
Sheet4 (2)

Cell Formulas
Range	Formula
I2:I10	I2	=LET(h, $V$2:$V$12, s, SEQUENCE(11), start_i, MATCH(MONTH($J2), MONTH($U$2:$U$12), 0), end_i, MATCH(MONTH(TODAY()), MONTH($U$2:$U$12), 0), f, FILTER(h, s=start_i), n, end_i-start_i+1, r, f*n, IF(r<=0, "", r) )

 

[bh24524]

Wow, that is exactly what I needed and completely stumps me lol. I've never seen that formula before and never would have come up with that. Great job Felix! If you ever have some time to do so, would you mind breaking down that formula into explanation? No biggie if it's too much to explain - I just love learning new formulas and how they work. That is not urgent though, so you could take your time on that.

Just a heads up that I might be replying to this very soon once I get confirmation with an additional question. I had one person tell me that it is the first of every month that these employees would receive the hour entitlement and I have another person telling me no, it's a month from their probation completion date. They have to have a meeting to determine which is correct, but if it is determined that it goes a month from their completion date and not the first, will that require a dramatic alteration of your formula?

 

[felixstraube]

Thanks for the feedback.

To your question on how the formula works.
First we use the LET function which lets you define variables (or names how MS calls them), in pairs of arguments, and the last argument is the returned value. You could read more on that here: LET Function

So the formula

=LET(a, 1, b, 2, a+b)

would assign 1 to variable a, 2 to variable b, and return a+b.
the values you assign to the variables can also be a formula like this:

Freq.xlsx
	A	B	C
1	1	2	14
Sheet9

Cell Formulas
Range	Formula
C1	C1	=LET(a, 3+A1, b, B1*5, a+b)

Then you need to know array formulas or dynamic array formulas. You can read about that here: Guidelines and examples of array formulas - Microsoft Support

Now to our formula.

=LET(
h, $V$2:$V$12, 
s, SEQUENCE(11),
start_i, MATCH(MONTH($J2), MONTH($U$2:$U$12), 0),
end_i, MATCH(MONTH(TODAY()), MONTH($U$2:$U$12), 0),
f, FILTER(h, s=start_i),
n, end_i-start_i+1,
r, f*n,
IF(r<=0, "", r)
)

we get the index number start_i from the probation completion date, and the index number end_i from todays month.
In f we store the hour number pero month (8.8 in our example for june).
n is the number of month we have to multiply by the hours.
and finally with the If statement we filter the values that are 0 or less.

Actually looking at the formula again, we could do this, no need for the sequence:

=LET(h, $V$2:$V$12,
start_i, MATCH(MONTH($J2), MONTH($U$2:$U$12), 0),
end_i, MATCH(MONTH(TODAY()), MONTH($U$2:$U$12), 0),
f, XLOOKUP(MONTH($J2), MONTH($U$2:$U$12), h, ,0),
n, end_i-start_i+1,
r, f*n,
IF(r<=0, "", r)
)

 

[bh24524]

Hi, I haven't forgotten about this, they were delayed in discussing how this should really work whether it's the 1st of each month or a month from the completion date. I'm thinking it will be some time next week, but I will post once I do get an answer.

 

[bh24524]

Okay, I did hear back on this and how we currently have it as entitling it at the beginning of the month is fine, so thank you very much for your help felix. There is another part to this that I wonder if it will require some VBA, but I will post that separate and later on. Thank you for the explanation too. That will give me some material to dig into with these formulas.

 

[felixstraube]

Thanks for the feedback. Glad I was useful to you.

 

[bh24524]

nvm thought there was an error. had posted but it seems it is okay.