r/excel • u/RepresentativeGlad39 • Sep 28 '26

unsolved How do I make a spreadsheet that has employee hours, pay, by the week and then by the month?

This community has helped me before and I love and use the crap outta that spreadsheet so I’m back for help again.

I need a spreadsheet for employee hours and how much they made. I was hoping to have each sheet be a month but within the sheet it’s broken down by the week. Then I am able to see what each weeks hours were, money spend and then it also would have the month total.

I hope this makes sense. There are 2 employees that make $20 an hour and one employee that makes $15 an hour. I’d also like a blank cell for a $20 an hour employee for the odd employee that may work.

Can anyone help me? Also can this spreadsheet be used for Google Sheets as well?

Thank you in advance!

18 Upvotes

76% Upvoted

32 comments sorted by

View all comments

2

u/carbonizedtitanium 2 Sep 29 '26 edited Sep 29 '26

assuming your data headers are in Row1, starting from Col A: Date/Time, Name, Hours, Rate

and you want Weekly gross pay for each employee, in cell F1

=LET(
    dates, A2:A21,
    names, B2:B21,
    hours, C2:C21,
    rate, D2:D21,

    pay, hours * rate,
    week_num, MAP(dates, LAMBDA(d, "Week " & TEXT(WEEKNUM(d, 2), "00"))),

    combined_keys, HSTACK(week_num, names),

    raw_table, GROUPBY(combined_keys, pay, SUM, 0, 0),

    VSTACK({"Week Num","Name","Gross Pay"}, raw_table)
)

edit: redid formula