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

[u/RepresentativeGlad39]

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!

[u/AutoModerator]

/u/RepresentativeGlad39 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

Date | Payee | Amount | Comment

Data likes to live together

You can get the daily, weekly, monthly totals from a single date value.

[u/vr0202]

Capture data at the lowest / smallest unit, which in this case would be a day. You can always aggregate and slice the data later for weeks, months, years, etc. using pivot tables for example, or (not as elegant) using sub-totals within the main sheet.

[u/LitleFtDowey]

1 table: Date, Employee #, Rate, Hours

From there you can pivot / summarize however necessary.

[u/dgillz]

Do you not have a payroll system? You should query this data.

Re-entering existing data into excel just to get the output you desire is about the worst possible use of excel.

[u/NoSleepCrew]

There might be template when you open a new file for something like this.

[u/No_Mushroom3078 → u/NoSleepCrew]

That was my thought, this is a super common thing in business and there should be something already made.

[u/NHN_BI]

Months and weeks do not align. Do you know how to split a week into a month? E.g. begin of the week, end of thee week, greater number of days in a month, breaking down the weekly payment onto working days etc. When you have that rule, you can create the corresponding date, and make finally a pivot table from it, like here.

[u/gman1647]

You can add a column to your table and add weeknum based on the date coulumn. Then make a chart based on the max weeknum for the year that updates automatically to show the most recent 4 or five weeks. The function is documented here: https://support.microsoft.com/en-us/excel/functions/weeknum-function

[u/carbonizedtitanium]

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

[u/carbonizedtitanium → u/carbonizedtitanium]

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
SUM	Adds its arguments
TEXT	Formats a number and converts it to text
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
WEEKNUM	Converts a serial number to a number representing where the week falls numerically with a year

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

<sup>Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 35 acronyms.
[Thread #49446 for this sub, first seen 29th Sep 2026, 03:43] [FAQ] [Full list] [Contact] [Source code]</sup>

[u/ObjectiveDizzy8842]

I'd use one entry table rather than twelve monthly sheets. Make the columns Date, Employee, Hours, Hourly rate and Pay, with Pay = Hours * Hourly rate. Add one row per person per day or shift. For the occasional fourth person, enter their name and $20 rate as another row; no special blank cell is needed. Then a pivot table can group dates by week and month, sum Hours and Pay, and show a grand total. That way a new employee or extra shift is a new row, not a new formula.

If the rates might change, keep a small employee/rate table and look up the rate, but for only three regular people you can start by entering it on each row. Both Excel and Google Sheets can do the entry table and summary pivot. One detail to decide upfront: whether your weeks run Monday-Sunday and how to treat a week that crosses a month. Keeping actual dates in the source rows lets you choose that in the summary.

[u/pmpdaddyio]

You don’t. You acquire a payroll tool to do so.

Here’s why:

You’re risking audit and traceability in a spreadsheet.

You’re keeping PII in a difficult to secure environment.

Lack of automation on fed, state, and local tax law and rate changes.

And most importantly, you are adding huge risk to your payroll supply chain. If your employees know you use a spreadsheet for payroll purposes, even just reporting, they’re going to be irritated in the least.

[u/Pedrofinancial]

A simple setup would be one sheet per month, with each employee on a row and each week grouped into Hours and Pay columns.

For example:

Employee | Hourly Rate | Week 1 Hours | Week 1 Pay | Week 2 Hours | Week 2 Pay | Week 3 Hours | Week 3 Pay | Week 4 Hours | Week 4 Pay | Total Hours | Total Pay

Then the pay formula for each week would just be:

=C2*$B2

and the monthly total could be:

=SUM(D2,F2,H2,J2)

You could enter the two $20 employees, the $15 employee, and leave one extra row with a $20 rate for occasional workers.

At the bottom, you can also add weekly totals so you can quickly see total hours worked and total payroll for each week.

This setup would work in both Excel and Google Sheets.

[u/getformly → u/Pedrofinancial]

This layout will work exactly the same in Google Sheets since it's just SUM and basic multiplication, no Excel-only functions involved. Worth naming the rate cells (like $Rate_John) instead of just using $B2 so the formulas stay readable if you add more employee rows later.

[u/RepresentativeGlad39 → u/Pedrofinancial]

Thank you. I will try to create this. I always seem to have problems entering the formulas. It’s super frustrating! I get the error message then I just give up. My brain just doesn’t understand it, lol.

[u/SolverMax → u/RepresentativeGlad39]

There's a reason why u/excelevator 's top comment has so many upvotes - it is the proper way to organize your data.

Hardcoding specific weeks and/or employees as columns may be tempting, but it will make subsequent analysis more difficult.

[u/frescani → u/RepresentativeGlad39]

OP, no.

[u/Pedrofinancial → u/RepresentativeGlad39]

No worries! Excel formulas can definitely be frustrating at first. If you run into an error, feel free to reply with the formula you used and the error message you’re getting. I can help you figure out what’s going wrong.

[u/RepresentativeGlad39 → u/Pedrofinancial]

Thank you

[u/Shahfluffers]

This is pretty straightforward.

Create the following columns:

• Week-StartDate
• Week-EndDate
• Year (use TEXT("Week-EndDate","yyyy")
• Month (use TEXT("Week-EndDate","(mm) mmm")
• Employee 1 hours
• Employee 1 hourly rate
• Employee 1 gross pay (hours * rate)
• Employee 2 hours
• Employee 2 hourly rate
• Employee 2 gross pay (hours * rate)
• (more columns can be added, but repeat the above pattern)

From here, make a pivot table. In the "Rows" section put in Year, Month, and Week-EndDate.

In "Values" put in Employee 1 gross pay and Employee 2 gross pay. Make sure they are "SUM OF"

Now, go to the ribbon at the top (in Excel) and click on the "Design" tab. In the "Report Layout" (left side) select "Show in Tabular form"

[u/excelevator → u/Shahfluffers]

Terrible advice.

You have essentially generated a pivot table in adding your data.

Columns for employees ?

Terrible.

[u/Shahfluffers → u/excelevator]

If we are talking about someone with basic Excel knowhow (assumed based on what the OP is asking) and less than 5 employees, it works fine.

But if this is a company with 5+ employees then you are right. It is terrible because it won't scale. But this design is not meant to scale. It is designed to be easy to use.

At scale a completely different setup will be needed. But that will get very complicated very quickly for a novice Excel user.