Need Excel help.

Nevakonaza

Nevakonaza

Nevakonaza

Soldato
Joined
7 Jan 2009
Posts
6,800
Hey guys,

Right,Please don't laugh :D.....never used Excel before but im looking to keep tabs on my working hours so i can check my wages to make sure they pay my overtime! :p

Now this is how im trying to do it.

exc.png


When i auto sum C3+D3,Im trying to get excel to calculate my start-finish hour for that day so i know how many hours i done...but as you can see its saying i done 20 hours.:p

I know its something im not doing right,but looking online at tuts it shows you all these complex things you have to type...i thought you could get it to auto calculate for you?

Appreciate the help a lot! :D
 
Copy this into the cells from E3 to E9 ...

=IF(AND(C3<>0,D3<>0),IF(D3<C3,D3+1-C3,D3-C3),"")

Format the cells C3-E9 as hh.mm (right-click > format cells > Custom > hh.mm).
 
shoot1st said:
Copy this into the cells from E3 to E9 ...

=IF(AND(C3<>0,D3<>0),IF(D3<C3,D3+1-C3,D3-C3),"")

Format the cells C3-E9 as hh.mm (right-click > format cells > Custom > hh.mm).
Click to expand...

Tried what you said,All the totals come to 5:00 :D ....obviously i done something wrong somewhere. :(

mranderson said:
Why would you autosum when you want the difference between two time points?
Click to expand...

Like i said in my first post,Its the first time ive used Excel. ;)
 
Nevakonaza said:
Tried what you said,All the totals come to 5:00 :D ....obviously i done something wrong somewhere. :(
Click to expand...

Type the formula as it is into E3 only.
Then select the cell, copy (or CTRL-C) and paste (CTRL-V) into the other cells, or you can do a FILL DOWN instead.

If you copied the formula as it was into cells E3-E9 the result would always be the same as the first row as the formula references the cells in the first row. If you caopy and paste the cell (or fill down) excel will automatically adjust the formula so it references cells on the same row.

If you've done it right, the formula in E4 will have references to C4 and D4, E5 would reference C5 and D5, etc.
 
Im not getting this at all,done the above exactly and all i get is random numbers added up that makes no sense.

If i type for instance 6 & 2 in two of the boxes and auto sum it calculates it to 8 for me so its adding up,But i just cannot get this damn thing to calculate time!
 
You can easily create a worksheet that correctly figures the hours worked for any shift. Follow these steps:


In A1, enter Time In.
In B1, enter Time Out.
In C1, enter Hours Worked.
Select A2 and B2, and press [Ctrl]1 to open the Format Cells dialog box.
On the Number tab, select Time from the Category list box, choose 1:30 PM from the Type list box, and click OK.
Right-click C2, and select Format Cells.
On the Number tab, select Time from the Category list box, choose 13:30 from the Type list box, and click OK.
In C2, enter the following formula:
=IF(B2<A2,B2+1,B2)-A2

If you enter 11:00 PM as the Time In and enter 7:00 AM as the Time Out, Excel will display 8, the correct number of hours worked.

 
Last edited:
As you're new to Excel how about this:

Input the times you start and finish work as decimals to the nearest 15 minutes in the 24 hour clock. So 06:30 becomes 6.50 and 5:45pm becomes 17.75.
Then just subtract start time from finish time. eg =C2-B2
So the answer would be 11.25 hours using the example times above.
 
Ethermaster said:
https://dl.dropbox.com/u/908850/Timesheet.xlsx

Just created this for you :) enjoy, hopfully its what you wanted.
Click to expand...

This is more like it thanks!

Just tried it,As you guys can see its calculating how many hours i do each shift,but for some reason its not adding up the wage part right?

hourz.png


Left out the "Lunch Break" and "Overtime" as..

Lunch break is paid for within the shift,and overtime pay remains the same as what i normal earn if im doing a standard shift...we don't get paid any higher for doing overtime. :)

Thanks guys.
 
Nevakonaza said:
This is more like it thanks!

Just tried it,As you guys can see its calculating how many hours i do each shift,but for some reason its not adding up the wage part right?

hourz.png


Left out the "Lunch Break" and "Overtime" as..

Lunch break is paid for within the shift,and overtime pay remains the same as what i normal earn if im doing a standard shift...we don't get paid any higher for doing overtime. :)

Thanks guys.
Click to expand...

Opps thats an error in my programme (just noticed a few acutally), When I get home I will fix it for you.
 
No problem :D Remember your have to take the tax off that yourself (could get confusing if I added a net take home after tax cell)
 
Last edited:
Yeah,The Tax & Nat Ins part did not bother me,Basically all i wanted is something that i could note down the hours ive worked,and total hours that week added up..
so the wage calculation was a bonus really just gives me a rough idea how much il pick up that week. :)

Thanks again dude. :)
 
You must log in or register to reply here.
Back
Top Bottom