SharePoint Calculated Column

Question

Currently we have an excel spreadsheet that keep track of overtime hours for employees.

Here is what it looks like:

Overtime Worked (hrs)	Overtime Taken (hrs)	Balance (hrs)
1.50	0.00
1.00	0.00
3.00	5.50
6.50	0.00
4.50	16.50
12.50
1.00	1.00
5.00	6.00
6.00	0.00

The way it works is pretty simple. Users enter the overtime hours in the “Overtime Worked (hrs)” column. The balance column keeps track of how much overtime hours they  have accumulated. Now when they are ready to cash out their OT hours, they enter in how many hours they are cashing out in the “Overtime Taken (hrs)”. The balance column then subtracts the value of overtime taken from the overtime worked to create a new total.

The formula in excel is as follows for the second row”

SUM=G2+[@[Overtime Worked (hrs)]]-[@[Overtime Taken (hrs)]]

Column G is the overtime worked column and in this example G2 would be the very first overtime entry 1.50.

I am now trying to create this in a SharePoint list. I am using a calculated column for Balance (hrs). The existing excel formula doesn’t seem to work.

What formula can I use for the balance column to accomplish the same behaviour in excel?

Thanks

Answer 1

Hi

let's name columns

Overtime Worked (hrs) = worked

Overtime tahen (hrs) = taken

The formula is

=1.5+[worked]-[taken]

You could also when you define the calculated column enter by gand the first part

=1.5+

and next select the column from right side the worked column

insert by hand -

and next select from right side taken column

This way, SHP will edit automatically the formula for you

---

Romeo Donca, Orange Romania (MCSE, MCTS, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

Answer 2

Ok  Im a bit confused...1.5 will not always be the value.

The first part is basically the current sum of all the overtime hours. I need this to be calculated automatically.

Answer 3

Hi berket13,

I am not sure, but how did you calculate G2?

Best Regards,

Sally Tang

Answer 4

Lets forget about the formula that I used for a second...

Can you see what I am trying to accomplish? Basically when the user enters in a value for overtime take it is suppoed to subtract it from the overtime balance.

Can you recommend a formula to make this work?

Thanks

Answer 5

Hi,

Why not use this?

Balance=[worked]-[taken]

Best Regards,

Sally Tang

Answer 6

Heres the problem:

Let say I input the following:

Worked                   Taken            Balance

5                                                       5

5                                                       5

                                  10                -10

The balance column should be adding the hours. So when I inputted the second entry for worked as 5, the balance should have become 10. Then when i take my 10 hours or cash them out it should be 10-10=0. And the balance is reset. So I guess the question is...how do we get the balance column to keep an up to date total based on the value in the previous row.