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.
Thanks. That's a start.
What this leaves me wondering, however, is how the weekly sheets are compiled. Are the numbers there, in each column, for each employee, simply entered into each of those weekly sheets at the end of the week? Or is there somewhere a daily process of capturing the hours? What does it look like? How is it connected with the weekly summary?
Yes, each week I fill a sheet in with the days off for the employees. I have all the other time off captured by the summary sheet. I just can't figure out how to do the vacation since it resets on the anniversary of their hire date.
This was tricky. Given the way that you record things--which isn't the way I'd do it, but let's not change it now--what I think you need to do is modify your weekly sheets. And it's there that the passing of an anniversary date will be dealt with, as follows
- you add the Hire date to the weekly sheet see column C of your 5-18-25 sheet (I only did it to one; letting you make the other modifications if you buy this solution)
- you continue to record vacation hours taken as you do now, now in Column J
- off to the side, in column Q in my "proof of concept" version of 5-18-25, the reportable number is shown. As the date of TODAY() passes any given anniversary date, thereby, the reportable number goes to 0.
- The formula, by the way, just compares the fractional year of today's date minus the hire date with the full year of today minus full year of the hire date; if the former is less than the latter, then it continues to show the week's vacation hours. If however, it's equal or greater, then it shows zero, effectively a "reset." For example, the very first person, hired in Nov of 2024. 2025-2024 =1, but subtracting the full Excel date from the full date of TODAY() and dividing by 365.25 (to account for leap years with long service employees), yields a number less than 1; so whatever is in the vacation hours is still reportable, and will be so until later in the year.
- The advantage of this is that you keep the historical record in column J of each weekly sheet, yet can always reconstruct if an issue is raised. The summary, meanwhile, now drawing from column Q in each sheet--once you've brought them all up to date--will always show the hours each employee has taken in their current "eligible year"
It might be possible to accomplish this with a far more elaborate formula that compared today's date with the date on each weekly sheet, for that one summary column only....that seemed unwieldy to me. This is more of a "behind the scenes" approach, but easily explainable.
This is perfect! I added the 2 columns and pulled in the count to the summary page. Thank you so much!!! I tried to figure this out for many hours and tons of AI formula generators, to no avail. lol
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.