Forum Discussion
Sum from cell across multiple sheet, depending on hire date
I have been testing the formula and it all works great except that it doesn't take into account the timesheet date in M4, it seems to be counting past the anniversary date.
For example, if I date the timesheet 10/1/24 in cell M4, Employee 1 still has a vacation time counted when it should have restarted on 11/11/24.
I think I figured out a way to have it work (attached). It's a roundabout way but seems to work.
You're very welcome. I will add that this was for me as well (I never pretend to be as expert as some of the others in this forum), it was a case of trial and error. I first wrote a formula that successfully set the summary page count to zero when it passed the anniversary date, but then realized that it wouldn't allow for adding additional data in "the new year" --
So something more radical was needed. Then it occurred to me that we could make something work "closer to the raw data," somehow do the "reset" at that stage to mark the transition into a new employment year, AND still keep collecting data pertaining to vacation days taken in that "new year." Then the trick was just to figure out how to easily recognize when the employment anniversary had passed. And for that I just played with the date math functions a bit.
I think my main suggestion to you--because you clearly have a solid basic understanding of Excel--is to give yourself permission "to play" a bit. One of the things I've learned over the years--you'll often see references to this fact on these pages--is that Excel has a lot of flexibility, in that there are often several different ways to get to the desired goal. I'm sure the solution I gave you is not the only one. If nothing else, there surely is a more "elegant" formula that could be written. I've found this website, ExcelJet, to be a great resource for researching new capabilities; you might enjoy just roaming through its many pages.
I have been testing it out and some older vacation days seem to be counted still for over a year. Are we able to maybe incorporate the week ending date of the timesheet to weed those out? I added a time sheet to the database dated 1/1/23 to test on.
I will definitely check that site out. I love tinkering with my existing databases and adding features. But this one, it too complicated for me. :)
I have been testing the formula and it all works great except that it doesn't take into account the timesheet date in M4, it seems to be counting past the anniversary date.
For example, if I date the timesheet 10/1/24 in cell M4, Employee 1 still has a vacation time counted when it should have restarted on 11/11/24.
I think I figured out a way to have it work (attached). It's a roundabout way but seems to work.
I went ahead and created a LET formula. Not sure it's any improvement, actually, but you can see how it works. You create a number of values, one at a time, and then write a very simple formula as the last entry in the LET. In this case the main formula is the last line, as before, a simple IFS function, using all the values previously defined.
=LET(
wkending,$M$4,
anniv,C9,
vachrs,J9,
tdy,TODAY(),
TooOldData,(tdy-wkending>360),
OldData,(tdy-anniv)/365.25>YEAR(tdy)-YEAR(anniv),
Curr,((tdy-anniv)/365.25)<((YEAR(tdy)-YEAR(anniv))),
IFS(TooOldData,0,Curr,vachrs,OldData,0)
)Let's try this. If indeed your actual database ends up holding weekly records that are more than a year old, then you will need to handle that by another condition. I've changed the primary function to an IFS function--which allows for multiple conditions and consequences.
See the attached.
=================
By the way, I use the value 360 to reject any data from a sheet that is more than 360 days older than TODAY(). You might prefer to make that 365. Test it with some sheets that carry week-ending dates on the boundary line of "too old." It wouldn't surprise me if there are some cutoff dates that are boundary-line ambiguous for one week. That is to say, any single week's summary might count days it shouldn't, or vice versa. I have not done any exhaustive testing of the many combinations of dates that are conceivable. You could play around a bit more with anniversary dates that are exactly one year before the date TODAY() to see what happens. It might be that one or more of the comparatives should be changed to "=>" or "<=" or the like. But that also depends on how absolutely precise the results need to be on any one day's summary. After all, the value of TODAY() will be different tomorrow, which means that those occasional ambiguities will resolve themselves.
================
A final note: This clunk formula could be made more elegant, using LET to define each calculation once, but for now, this clunky one allows you to read it (I hope) more clearly. And it could be even more elegant if you used LAMBDA to define your own function using the three dates as inputs. Those are some learning opportunities for you.