Share via

Please help me create a shortcut to make searching for data and getting totals faster for my payroll process

Anonymous
2022-03-12T23:16:11+00:00

Hi!

I am a payroll representative assigned to specific nurses within a hospital facility. Every week, the hospital facility sends us an excel spreadsheet that includes every nurse's total hours worked within each date and per pay code. I have to search through the spreadsheet to find my assigned nurses and for each nurse, I have to find out how many different days that they worked within the week and their total hours worked within that date per pay code.

For example, this is how the spreadsheet columns and rows are shown:

Employee # First Name Last Name Date Hours Pay Code Organization Job Class
123456 John Doe 01/01/2022 13.5 Reg 1234 RN1527
123456 John Doe 01/02/2022 13.5 Reg 1234 RN1527
123456 John Doe 01/03/2022 13.0 Reg 1234 RN1527
123456 John Doe 01/03/2022 0.5 OT 1234 RN1527
123456 John Doe 01/04/2022 12 Call 1234 RN1527

And after all of John Doe's total hours per date and per pay code are listed, the next nurse starts. The list contains all nurses within that facility and not just the ones that I am assigned to. So I am having to go through the list and find my specific traveler.

For example, after I find John Doe's name, from the information on the above table's example, I have to find out how many different dates he worked (not to include duplicate dates) and how many total hours were worked per pay code.

So from this table, I have gathered that John Doe worked 4 different days (01/03/2022 is duplicated because his total hours ran into over time hours in that same shift), and within those 4 days, he worked a total of 40 Reg, 0.5 OT, and 12 Call hours for this work week.

From that, I enter that information on my own excel spreadsheet next to John Doe's name so that I can let him know how many hours his facility is reporting that he worked to make sure that is what needs to be entered to pay out to him and how many shifts he worked in order to pay his meal/housing per diem which is paid out per shift they work.

So my question is, is there a way to make this process faster? By finding only my assigned travelers and from each traveler, getting a total of their hours worked per pay code for the week, and how many days they actually worked without adding an extra day from the duplicated date?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-13T03:34:35+00:00

    Re: many nurses

    The formulas were based on the data you posted.
    They can be adjusted to accommodate all of the data.

    Also, It is not necessary to filter the data, but as I see it, a separate list of your assigned nurses would be needed.

    It could take more effort to finish it up than I am willing to spend, there are many at this forum who may willing to step in.

    '---

    Nothing Left to Lose

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-03-13T01:10:05+00:00

    Hi!

    Thank you for responding. Is there a way to get this to work when I have nurses that list data past row 6? Because let's say I filter their names to only see one name/employee number at a time, if their name/data was originally on rows 13-17, when I filter them, their row numbers do not change.

    Therefore the formula starting at row 2 and ending at row 6, wouldn't grab the totals I need from my nurse that has their data on rows 13-17.

    For Example of the row numbers not changing when I filter them to only show one specific nurse at a time:

    Thank you again!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-03-13T01:02:18+00:00

    Hi Alex!

    Thanks so much! I do have a question about this. Is there a way for this work if I have multiple nurses on this list and lets say their data was listed all the way to row 17?

    For example, I tried your formula and instead of the data being gathered ending on row 6 for everything, I had it gather data from row 2-17 for everything. This did not work.

    Or will I need to find each one my nurses from the master list and paste one at a time only their name/data on the excel sheet that has this format?

    Thank you again!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-03-13T00:11:52+00:00

    Hi Maddie,

    Not sure if this might be helpful.

    Based on my idea on your requirement. You can setup formulas to make the work easier.

    Formula in C12:

    =COUNT(UNIQUE(FILTER(D2:D6,(B2:B6=A12)*(C2:C6=B12))))

    In D12

    =SUMIFS(E2:E6,B2:B6,A12,C2:C6,B12,F2:F6,"Reg")

    In E12

    =SUMIFS(E2:E6,B2:B6,A12,C2:C6,B12,F2:F6,"OT")

    In F12

    =SUMIFS(E2:E6,B2:B6,A12,C2:C6,B12,F2:F6,"Call")

    In this way, if you need to track different nurses, just change the First and Last name in A12 and B12. It will refresh the data automatically.

    Regards,

    Alex Chen

    Was this answer helpful?

    0 comments
  5. Anonymous
    2022-03-12T23:54:49+00:00

    Re: faster?

    You can filter the list by Employee Number and paste the filtered list on a new sheet. Then...

    (columns I and J were added)

    FORMULAS in COLUMN J…

    '=SUMPRODUCT(--($A$2:$A$6=$A$2),--($F$2:$F$6=I2),$E$2:$E$6)

    '=SUMPRODUCT(--($A$2:$A$6=$A$2),--($F$2:$F$6=I3),$E$2:$E$6)

    '=SUMPRODUCT(--($A$2:$A$6=$A$2),--($F$2:$F$6=I4),$E$2:$E$6)

    Maybe a possible start?

    '---

    Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (free excel programs)

    Was this answer helpful?

    0 comments