Marks
Marks

Reputation: 175

How to dynamically calculate the overtime left and the overtime used without a circular reference in Excel?

The goal is to fix a circular reference in my logic in this "two weeks pay" input workbook. It's a temporary sheet for when people are outside the office and can't access the system. That said, that sheet still should give them accurate data.

There's 26 sheets that contains the times done by an employee for example, in a typical format, for 2 weeks in each (a year in total). Stripped of all formatting and non-useful information for this enquiry, they would look somewhat like this (with proper dates) :

    +-----------+----------+--------+----------+--------+-------+------+
    |   Date    | AM start | AM end | PM start | PM end | total | over |
    +-----------+----------+--------+----------+--------+-------+------+
    |   Monday  |   8:00   |  12:00 |   13:00  |  16:00 | 7:00  | 0:00 |
    +-----------+----------+--------+----------+--------+-------+------+
    |  Tuesday  |   8:00   |  12:00 |   13:00  |  15:00 | 6:00  | 0:00 |
    +-----------+----------+--------+----------+--------+-------+------+
    | Wednesday |   8:00   |  12:00 |   13:00  |  17:00 | 8:00  | 1:00 |
    +-----------+----------+--------+----------+--------+-------+------+
    |    ...    |   ....   |  ....  |   ....   |  ....  | ....  | .... |
    +-----------+----------+--------+----------+--------+-------+------+

Then on another sheet, there's some calculation has to what is the paid amount (maximum 70 hours per 2 weeks), the overtime done that has to be paid, etc.

          A          B           C            D                   E                F              G           
      +-------+------------+----------+---------------+-----------------------+-------+---------------------+
    1 | Pay # | Hours paid | Overtime | Used overtime |  Total hours worked   |       | Total overtime left |
      +-------+------------+----------+---------------+-----------------------+-------+---------------------+
    2 |   1   |    70:00   |   5:00   |      0:00     |         75:00         |       |         0:00        |
      +-------+------------+----------+---------------+-----------------------+-------+---------------------+
    3 |   2   |    70:00   |   0:00   |      5:00     |         65:00         |       |                     |
      +-------+------------+----------+---------------+-----------------------+-------+---------------------+
    4 |  ...  |     ...    |    ...   |      ....     |                       |       |                     |
      +-------+------------+----------+---------------+-----------------------+-------+---------------------+

In the above table, the pay #2 got 70 hours paid, but the person would have done only 65 hours and used 5 hours of the overtime done the past two weeks. A1:E4 is connected together, G1:G2 is data by itself, not linked to the pay numbers or other data in that sheet (in other words, there is only one cell that contains that total overtime left and F is used to separate both tables). G2 currently have 0:00 because the 5:00 it would have had has been used to complete the second pay.

The Hours paid cell (B) contains this formula :

=IF($E$2>=2.91666666666667,2.91666666666667,IF((2.91666666666667- $E$2)<=$G$2;2.91666666666667,$E$2+$D$2))

The important part here is to remember that B needs D, hence why I explained it's formula.

The Overtime (C) and "Total hours worked" (E) cells contains basic formulas that either gives the amount of time over 70 hours or the total hours worked; no need to explain it here, it works.

The Used overtime cell (D) is where it gets tricky. To explain it, we'll need to know what's up with G2.

The Total overtime left cell (G2) is the total of overtime hours minus the sum of all cells in D (used overtime). It's purpose is to get an up to date value of how much overtime there is left to be paid.

Back to Used Overtime. You probably start to see circular reference here; D needs G2 to work and G2 is the sum of all cells in D (in the table range, not all of them). The formula requires the notion of how much overtime there is left so it can check if we can use some. Here's the formula :

=IF($E$2>=2.91666666666667,"00:00",IF((2.91666666666667-$E$2)<=$G$2,(2.91666666666667-$E$2),IF(($G$2+$E$2)<=2.91666666666667,$G$2,"00:00")))

How could I both have the accurate overtime left and yet have the used overtime both dynamically calculating themselves without a circular reference ?

Upvotes: 1

Views: 103

Answers (1)

Jacob P
Jacob P

Reputation: 148

What if every row had an up to date value for Total Overtime Left after that pay period?

Formula for G2: =C2-D2

Then every G cell after that only needs to add the Overtime Left from the previous pay period + overtime - used overtime:

G3: =G2+C3-D3

And it just goes on from there.

Upvotes: 1

Related Questions