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.
Sure, here is a sample file. I appreciate the help!
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.
