Addition formula.

[figuare9]

I'm currently trying to create a formula that will allow me to enter in sick days for employees. I'm a little bit stuck with it though at the moment, and could really use a hand..

Basically, Every month an employee "earns" one sick day, up to a maximum of 60. At the beginning of the year, I'd like to enter the amount that they have starting in the beginning balance.

So For instance, cell D5 & E5 (Merged) is the beginning balance. Then in every other Cell F5, H5, J5, etc.. I'll enter in their "excused" sick days per month. And of course, cells next to them will be "unexcused" sick days. Cells G5, I5, K5, etc..

What I'd like is that in cell AF5 to show me the total days they have available, but I'd like it to automatically calculate the accruing days the employee earns every month. So if start with 50 sick days, and I enter in 3 sick days for Jan, They should have 48 sick days available at the very end of Jan. (Since they earn one every month). The days are entered at the very end of the month, so the days available "Todays Date" Should only be shown AFTER entering data for the month. If that makes sense.. lol

Also, an excused and unexcused count the same, but are tracked elsewhere. I guess, the easiest way to see is to just look at the picture.

So if I start with 50 sick days, and enter 4 excused, and 1 unexcused, (And I only enter in January) I should see 46 available days because it should automatically calculate the one accruing day.

This is what I have..

=IF(OR(F5="",)*OR(G5="")*OR(H5="")*OR(I5="")*OR(J5="")*OR(K5="")*OR(L5="")*OR(M5="")*OR(N5="")*OR(O5="")*OR(P5="")*OR(Q5="")*OR(R5="")*OR(S5="")*OR(T5="")*OR(U5="")*OR(V5="")*OR(W5="")*OR(X5="")*OR(Y5="")*OR(Z5="")*OR(AA5="")*OR(AB5="")*OR(AC5=""),"",MIN(60,MAX(0,SUM(-F5,-G5,-H5,-I5,-J5,-K5,-L5,-M5,-N5,-O5,-P5,-Q5,-R5,-S5,-T5,-U5,-V5,-W5,-X5,-Y5,-Z5,-AA5,-AB5,-AC5,D5,))))

But it's not calculating accruing days. Not really sure how to do that.. This picture here, should show me 45 days available.

 

[NiMip]

Re: Looking for help with an addition formula.

First thing: multiplying your OR statement together is effectively doing AND(F5="",G5="",H5="",etc.), so that might cut down on a bit of text.

But in terms of what you're actually after, does this do the job? =MIN(60,D5-AD5-AE5+COUNT(F5,H5,J5,L5,N5,P5,R5,T5,V5,X5,Z5,AB5))
Since you've already calculated the sum of both Excused and Non-Excused in AD5 and AE5, you can just use these for the summing of sick days used. The COUNT part will add a sick day everywhere there's a value in the Excused cells for each month. Note that this means you will need to enter something for each month, even if it's just a 0.

I could get a bit more creative if you want it to figure out how many sick days have been accrued based on today's date if you don't want to have to enter zeroes?

 

[figuare9]

Re: Looking for help with an addition formula.

Is there a way we could stop it from going negative? So it stops at zero. I'd like to use conditional formatting to essentially pop up an alert when it hits zero. For the sake of less errors I suppose. Also, does this accommodate unexcused days? I'd like the unexcused days to also work just like the excused days. I'll also use conditional formatting there as well when it gets over our company policy.


As far as a readout based on today's date... Is that possible with the current layout of the sheet!? If so that would definitely be really cool. If it matters, this will all be for the year 2018.

 

[NiMip]

Re: Looking for help with an addition formula.

Is there a way we could stop it from going negative? So it stops at zero.

Yes absolutely: =MAX(0,MIN(60,D5-AD5-AE5+COUNT(F5,H5,J5,L5,N5,P5,R5,T5,V5,X5,Z5,AB5)))

Also, does this accommodate unexcused days? I'd like the unexcused days to also work just like the excused days

Yes it does. It's simply calculating "beginning balance" - "excused" - "non excused" + "months with 'excused' value".

As far as a readout based on today's date... Is that possible with the current layout of the sheet!?

Anything is possible
I think I've got your formatting right... the month headings are in row 2, with 2 cells merged, yeah?
One way would be =MAX(0,MIN(60,D5-AD5-AE5+(MATCH(TEXT(TODAY(),"MMMM"),$F$2:$AC$2,0)-1)/2))
This will automatically add a sick day on as soon as you roll over into the new month, whether you've entered a value in or not. So on 31st March it will only have accrued 2 sick days, 1st April it will have accrued 3.

 

[figuare9]

Re: Looking for help with an addition formula.

Okay, so I've tested that formula, and it's not quite there. Is it calculating the days for the entire year? I noticed after trying it, it shows me too many days. For instance, I only entered data in Jan, Feb, March.. Started with 45 days, one sick day for jan-Mar and it displays 52 days available. Instead, it should give me 45. However, the first formula on your last post is working PERFECTLY! So it must've been a misunderstanding.

It'll definitely do the way that it is now. Because the data is only entered at the end of the month. Although, it would definitely be nice to have everything on the same sheet (such as the days the employee is actually sick), I don't see how it's possible without more screen space. As of right now, if an employee asks how many sick days he has available I can't give him that information mid month without looking at my hand written sheet first. The data before this data is writtin so to speak. :P Although I could just keep a running tally in the month I guess.

Anyways, thanks NiMip. that was extremely helpful, and I'm definitely grateful for it!

 

[figuare9]

Re: Looking for help with an addition formula.

Oh, shoot.. After a bit of testing, I just noticed this doesn't work with non-excused. The current formula doesn't accrue days when you enter in a non excused day. So in example, an employee only has 1 unexcused sick day for the month. He doesn't earn a day with this formula. It's simply just subtracting one. We're soo close! lol I tried to add the non excused days to the count, and it works "KIND OF" but then after testing it, if I have 1 excused day and 1 non excused day (A total of 2) it ends up adding both of them, and not subtracting (1) day. if that makes sense.. lol

one excused day and one non excused, should only subtract one available sick day, since we're calculating the accruing day in the formula.

=MAX(0,MIN(60,D5-AD5-AE5+COUNT(F5,G5,H5,I5,J5,K5,L5,M5,N5,O5,P5,Q5,R5,S5,T5,U5,V5,W5,X5,Y5,Z5,AA5,AB5,AC5))))

So in short, I guess if we started with 45, and had 1 excused day, and one non excused day..

This would be 45-1-1+2 (count) When it should be 45-1-1+1 (No freakin clue what needs to make this work) lol.

 

[NiMip]

Re: Looking for help with an addition formula.

So to your second last comment, the way I've written the formula is that it's based on TODAY(), which is whatever day we're actually on. So it's calculating 52 because we're in December, and it doesn't matter what data you've entered... in hindsight, probably not great for testing if the spreadsheet works. I also over-complicated it a bit with the whole MATCH bit. This is probably better, which reads "today's date" from cell AF4, noting that you will need to enter a date into cell AF4 for it to work.
=MAX(0,MIN(60,D5-AD5-AE5+(MONTH($AF$4)-1)))

When you're actually running this spreadsheet rather than testing it, I would suggest AF4 =TODAY() so it auto-updates whenever you open the spreadsheet and you don't have to manually enter the date in.

Oh, shoot.. After a bit of testing, I just noticed this doesn't work with non-excused. The current formula doesn't accrue days when you enter in a non excused day. So in example, an employee only has 1 unexcused sick day for the month. He doesn't earn a day with this formula. It's simply just subtracting one. We're soo close! lol I tried to add the non excused days to the count, and it works "KIND OF" but then after testing it, if I have 1 excused day and 1 non excused day (A total of 2) it ends up adding both of them, and not subtracting (1) day. if that makes sense.. lol

one excused day and one non excused, should only subtract one available sick day, since we're calculating the accruing day in the formula.

=MAX(0,MIN(60,D5-AD5-AE5+COUNT(F5,G5,H5,I5,J5,K5,L5,M5,N5,O5,P5,Q5,R5,S5,T5,U5,V5,W5,X5,Y5,Z5,AA5,AB5,AC5))))

So in short, I guess if we started with 45, and had 1 excused day, and one non excused day..

This would be 45-1-1+2 (count) When it should be 45-1-1+1 (No freakin clue what needs to make this work) lol.

I'm a bit confused by this, but I'm still working with my latest formula, which I believe should work for you now.

Just to clarify my understanding... let's say we're currently in February and the person had 45 sickies at the start:
One excused sick day in Jan, they still have 45 (45 - 1 excused - 0 non-excused + 1 accrued = 45)
One non-excused, they still have 45 (45 - 0 excused - 1 non-excused + 1 accrued = 45)
One of each, they have 44 (45 - 1 excused - 1 non-excused + 1 accrued = 44)
Zero sick days used, they have 46 (45 - 0 excused - 0 non-excused + 1 accrued = 46)

This is correct yeah?

 

[NiMip]

Re: Looking for help with an addition formula.

Actually I think I better understand what you mean in the second comment now. For my original formula, you have to put something in for excused sick days, even if it's just a zero. Putting in a 1 for non-excused will not flag an accrued day for the month. You could work around this with some ORs...

...but, my new formula doesn't have that issue as the sick-days-accrued is based on date, not data entry, so I would recommend going with the new

 

[figuare9]

Re: Looking for help with an addition formula.

This is great. You really helped me out here. Thank you soo much stranger!! I hope to pass it along some time. Much appreciated!!!

 

[figuare9]

Re: Looking for help with an addition formula.

.... I managed to find another issue with this... haha.. I'm sorry..

Okay so I tried for a while to figure this out, but can't seem to make sense of it.. See pic for example.

So, ultimately, if an employee starts the year with 60 days available, it looks like the formula is still adding days over the 60, just simply not displaying them. So the employee should have 60-30+2. Or a total of 32 days available. Not 41. This is using your latest formula. While using =Today() in AF4.