Time sheets: decimal to hh:mm

[gray@west]

I'm trying to create a time sheet for employees to fill out. I have created from scratch b/c we want to know when employees are taking breaks, not just lunch. I also found that a lot of free templates have overtime - which is not an option for part-timers!

(spreadsheet attached with example times)

I have figured out how to calculate all of the times correctly. There are two "tables" on the sheet, one is time in/out and lunch hour, the other one is just for breaks. I do not have the two combined because we are not deducting breaks from their pay, just simply want to see how much time is spent on break. I understand that putting the two tables together would probly eliminate my problem.

The breaks are calculated in hh:mm (employee had 1hr and 45min of breaks, 1:45), but the regular hours worked are in decimal form (in my example the employee worked 35.25 hours, or 35 hours and 15 minutes), and I would like it to display in HH:MM (or 35:15).

I would like for F11 to be in HH:MM format. I have tried custom formatting the box, but h:mm gives me 6 (incorrect), and [h]:mm:ss gives me 846:00:00 (also incorrect). The break "table" uses the TEXT function, is there a way to incorporate that into the regular hours table? I have tried putting it in various places but nothing has worked.

Sorry this is so lengthy, trying to be thorough! I've been searching the forums all day for a question similar, but haven't been able to decipher any answers. Thanx!

[Whizbang]

35.25 converted to time format is acutally converting as days. Divide your number by 24 to convert it to hours.

So, in F11 use the formula =SUM(F6:F10)/24

[daddylonglegs]

I concur with Whizbang, also using TEXT function is almost always a bad idea. As the name implies it returns text formatted results which can't be easily used for calculations. In N6 use just

=I6-H6+(L6-K6)

and format the cell as h:mm

Then you can just use a SUM formula in N11

[gray@west]

Whizbang: I tried changing the formula to =SUM(F6:F10)/24 like you said, but it gives me 1.46875. I don't want it converted to days, I still want it in hours, just 35:15.

Daddylonglegs: Thank you, I tried it and it did work. Would have been helpful two weeks ago when I initially put this together. Unfortunately, that is not the table I am trying to fix!

Should I be changing the formulas in F6-F10 or something? It would be helpful if those also were in hh:mm format.

[Whizbang]

Whizbang: I tried changing the formula to =SUM(F6:F10)/24 like you said, but it gives me 1.46875.

That is exactly what you want. Now just format that cell as HH:MM and you are all set.

I don't want it converted to days, I still want it in hours, just 35:15

This is not converted TO days. When you convert 35.25 to time format, Excel takes that to mean 35.25 days, which equals out to 846 hours. Which is why you got 846:00:00 when you formatted it as [h]:mm:ss. By dividing 35.25 by 24, you you are telling Excel you have 1.46875 days. Displaying 1.46875 in HH:MM format displays 35:25.

[daddylonglegs]

If you use

=SUM(F6:F10)/24

as per Whizbang's suggestion then you need to custiom format F11 as [h]:mm. The square brackets allow display of "elapsed time", i.e. over 23:59

[gray@west]

Awesome awesome awesome
I originally had F6-F10 each x24 to get the number of hours, so I took that out of the formula and formatted each of the cells to [h]:mm, worked perfect. Thank you for breaking it down into terms I could understand.