# timesheet

#### donDIRT

##### New Member
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?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Colin.

I dont really have one, its just =sum

Book1
ABCD
1DayStartFinishtotal
2Mon7:45
3Tue7:30
4Wed7:45
5Thu
6Fri
7Sat
8Sun
923: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.

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

anyone able to help me with that?

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

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

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

Replies
1
Views
124
Replies
6
Views
172
Replies
1
Views
93
Replies
2
Views
162
Replies
7
Views
213

1,203,552
Messages
6,056,059
Members
444,841
Latest member
SF_Marnie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back