Excel工时表格公式求助：员工工时划转与负数校验

Hey there! Let’s work through your time tracking table problem together. Managing hour transfers between team members while keeping everyone’s hours non-negative and hitting that 1800-hour team total is totally doable with a few simple Excel functions. Here’s how to implement each part:

1. Deduct Hours from Worker1 (H Column) #

To make sure Worker1’s hours never drop below 0 after the transfer, use the MAX function—it’ll cap the result at 0 if the deduction would make it negative.

Assuming:

• Worker1 is in row 2
• The number of hours to transfer is stored in cell K2 (you can pick any cell for this, just keep it consistent)
• Base hours per employee are fixed at 200

The formula for Worker1’s H column (cell H2) would be:

=MAX(200 - K2, 0)

If your base hours are stored in another column (say, column B where B2 is Worker1’s base hours), adjust it to:

=MAX(B2 - K2, 0)

2. Add Corresponding Hours to Worker2 (H Column) #

This is straightforward—just add the transfer hours to Worker2’s base hours. Using the same K2 cell for transfer hours:

Worker2 is in row 3, so the formula for H3 is:

=200 + K2

Or if using a base hours column:

=B3 + K2

3. Non-Negative Validation #

The MAX function in Worker1’s formula already handles this—it ensures their hours can’t go below 0. For other team members who aren’t involved in transfers, their hours will stay at the 200 base (or their assigned base), so no negatives there either.

Bonus Tip #

Storing the transfer hours in a single cell (like K2) makes it easy to update later—you only need to change one value instead of editing multiple formulas. If you have multiple transfers between different employees, you can repeat this logic for each pair, always using MAX to protect against negative hours.

内容的提问来源于stack exchange，提问作者Dijana Damjanić