Forum Discussion
Solved
Sum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day.
Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary")
Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary")
In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again.
Any help will be much appreciated!
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.
15 Replies
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.
Hi,
Below is a possible solution as per my understanding.
Formula on C3 to C5 ...
On C3
=COUNTIFS(INDIRECT("'" & A3 & "'" & "!A2:A5"),">=" & DATE(YEAR(B3)+$B$1-1,MONTH(B3),DAY(B3)), INDIRECT("'" & A3 & "'" & "!A2:A5"), "<" & DATE(YEAR(B3)+$B$1,MONTH(B3),DAY(B3)))
On C4
=COUNTIFS(INDIRECT("'" & A4 & "'" & "!A2:A5"),">=" & DATE(YEAR(B4)+$B$1-1,MONTH(B4),DAY(B4)), INDIRECT("'" & A4 & "'" & "!A2:A5"), "<" & DATE(YEAR(B4)+$B$1,MONTH(B4),DAY(B4)))
Here is the explanation
Requirement - count to reset on their hire anniversary month and day.
B1 (user input) - is the Calander year to be consider for each employee from the hired date.
For example,
For Employee 1, 1 in B1 is for date 01/02/2001 to 31/01/2002 & 2 for 01/03/2002 to 31/01/2003.
This will be applicable for all remaining as per their hired date.
This can be changed by adding years in columns.
Hope this may help.
Are you willing to post an actual workbook that replicates the way your real one is organized. Include at least two employees but not real names (e.g., Mickey Mouse, Donald Duck, etc), no real Soc Sec Nos, etc. It would make it a lot easier for us to help you if we could see what you have rather than work from your verbal description.
Let me add a postscript to my request for a sample workbook: I ask as a person who was the Director of the HR/compensation database for a major corporation back during my working years (retired now). I have a hunch that you might benefit from rethinking how you've designed your workbook. I may be wrong but either way, you'd be helping us help you by showing us how you've approached this overall task.
I am limited on how it's structured since I am using actual timesheet that get submitted each week. I am not able to modify the format on those.
May consider below:
- Aggregate Vacation Days Across Sheets: You’ll need a formula that pulls the vacation days from multiple sheets.
- Check Anniversary Reset Condition: Identify when the hire date anniversary occurs and reset the count accordingly.
Sample for your reference:
=SUMIFS(INDIRECT("'" & A1:A10 & "'!F5"), INDIRECT("'" & A1:A10 & "'!A5"), ">= " & DATE(YEAR(TODAY()), MONTH(B5), DAY(B5)))
