timesheet

[donDIRT]

Hi, I have a couple of questions here. I'm working on a timesheet for work. I'm trying to figure out how to make it calculate properly. I clock in my time when i arrive and when I leave. It calculates hours and minutes worked every day, and total hours and minutes at the end of the week. when it adds the hours and minutes for the whole week, i think its calculating wrong. Like, if i work 7 hours 45 minutes monday, 7 hours 30 minutes tuesday, and 7 hours 45 minutes on wednesday, when excel adds that up it comes to 22 hours and 20 minutes. If you add it on paper, and rounding at 60 instead of 100 (for 60 minutes/hour) it comes to 23 hours. What formula will make it calculate correctly?

 

[rs2k]

Can you post your formula?

Colin.

 

[donDIRT]

I dont really have one, its just =sum

 

[rs2k]

Book1
	A	B	C	D
1	Day	Start	Finish	total
2	Mon			7:45
3	Tue			7:30
4	Wed			7:45
5	Thu
6	Fri
7	Sat
8	Sun
9				23:00
Sheet1

The formula in D9 is =SUM(D2:D8)
the cells are formatted as [h]:mm

I think you may be back with more questions

Colin.

 

[donDIRT]

hmm, ok, maybe my problem doesnt start there lol. ok, what i've done is, in order to get it to calculate right, i had to enter the clock-in times in military time, and even further, its not calculating as time, i enter the time i arrive as a decimal number. If i arrive at 9:10, I enter as 9.10, and leave at 15.55. Cant figure out how to make it calculate the time correctly if I enter the numbers as times instead of decimals. so instead of adding 7:45, 7:30, and 7:45, its adding 7.45 hours, 7.30 hours, and 7.45 hours, and thats where the problem lies. I guess if i can figure out how to make it calculate this correctly: clock in 8:00 am clock out 3:45 pm = 7:45, the rest wil be easy

 

[donDIRT]

anyone able to help me with that?

 

[daniels012]

If you add the letters A for am and P for PM you can make it work.
In cell A1 = 8 a (That is type 8 then a space and then a)
In cell B1 = 9:45 p (You have to type out time increments a space then p)
In cell C1 = =(B1-A1)*24 Be sure to format the cell as General (NOT TIME)

This is how I get it to work.

Michael

 

[donDIRT]

thanks for all the help. I have one more question, i'm such a newb at excel. Ok, now i have my results as 39:05 hours, i'm wanting to convert this to a decimal which i can do with =product(L7, 24) and put it as a number format with 2 decimal places, but I would also like to round this number to the nearest quarter, or .25. so 39:05 becomes 39.08, and then round to the nearest quarter would just round it down to 39 hours. or if it was higher number, say 39.17, would round it up to 39.25. Is there a way to combine both of these functions? I know how to do them both separately, but something like =ROUND((L7/0.25,0)*0.25, Product(L7, 24)) I know thats not right, but if something like that can be done, how would i do it? thanks in advance

 

[barry houdini]

With Analysis ToolPak you can use MROUND

=MROUND(L7*24,0.25)

or just with ROUND

=ROUND(L7*96,0)/4

btw if you enter your times with a colon e.g. 08:00 and 15:45 then excel will be able to calculate the difference correctly