array formula for timesheet

Question

I need a formula that will lookup the amount of hours that should have been worked for that shift then tell me how many extra hours have been worked.  

the formula i am trying just now is:

VLOOKUP(C2,'Shift Times'!$A$1:$D$10,4,FALSE)=SUMIF(G2>'Shift Times'!D2,'Nov - Dec (2)'!G2-'Shift Times'!D2)

one of many i have tried after trawling through reams and reams of help answers.

My timesheet is set up to record my times monthly.  i have a vlookup in place to enter in the required working times according to my shift.  i have worked out how to calculate how many hours i have worked but also need to work out how many hours overtime i worked.  my hope is that there is a formula that will look up how many hours i should have worked then tell me how many i have, minus the regular time.

Answer 1

Sharon,

Based on my understanding of your problem from the revised file,

pls try the following formulas and see if you get the desired answers for various combinations.

Pls let me know if there is any thing going not as expected.

In cell G2  copy the formula     

    =IF(C2="O","",IF(C2="SA",0,MAX((F2-E2-"0:45"),VLOOKUP(C2,'Shift Times'!$A$1:$D$10,4,0))))

In cell H2  copy the formula     

    =IF(C2="O","",IF(C2="SA","12:00"-E2,""))

In cell I2  copy the formula     

    =IF(C2="O","",IF(C2="SA",F2-"12:00",""))

You can then copy these formulas down below for all the other scenarios/combinations.

Hope this is Helpful.

Answer 2

@ Sharon,

I got your sample files from OneDrive.

From the 'test timesheet 1.xlsx'   I have some queries :

1. In the cell E2 you have put a VLOOKUP formula which pulls the 'Start Time' from the 'Shift Time' worksheet

   Similarly, the formula in cell F2 pulls the 'Finish Time' from the 'Shift Time' worksheet

   Where is the Actual time worked for row number 2 ?

   Unless we have the actual start time and actual Finish time, how can we compute the total time worked and

   unless we have total time worked, how can we compute the Overtime worked.

2. The rules for overtime  (Time & 1/2    and   Double)  are not very clear - pls explain once again - with examples.
3. On the whole, give some manually calculated desired results with corresponding examples.

Answer 3

Hi Rajesh

Thanks for getting back to me.  i think i have sussed out how to get a sample of my timesheet onto the sharedrive and should be at the following link.

https://onedrive.live.com/?id=474FDADF6FD0311A%21137&cid=474FDADF6FD0311A

Hope this helps

Kind Regards

Sharon

Answer 4

Hi, see Chip web with all formulas related to timesheet

http://www.cpearson.com/excel/overtime.htm

Answer 5

Not sure what you are trying to achieve by the formula VLOOKUP(C2,'Shift Times'!$A$1:$D$10,4,FALSE)=SUMIF(G2>'Shift Times'!D2,'Nov - Dec (2)'!G2-'Shift Times'!D2)  which you are trying.

However, I feel your problem is certainly solvable.

But to help you in that, we need to have a closer look at your data.

You need to help us, for us to help you in turn.

Can you upload your .xlsx file on OneDrive (after replacing your confidential data, if any) and share the link here.

If for some reason, this is not possible then alternatively you can paste a screen-shot of your data file with the column letters and row numbers clearly visible alongwith your data.

Also highlight the place where you want the output result.

If possible try to give an example with desired answer.