Results 1 to 5 of 5
Thread: Broken time sheet

20090607, 00:49 #1
 Join Date
 Jun 2009
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
My HR department gave me a simple time sheet form that was broken, and since I have to learn Excel anyway I'm trying to fix it as a good learning project. It's a simple timesheet: calculate how many hours between start time and end time, then subtract lunch. (I believe I've fixed the other errors.) The formula I came up with works fine for a 95 work day, but not for a 7:454:30 workday, which is standard here. I suspect the problem is either something really obvious (like a typo I haven't been able to spot for a week) or really subtle (and beyond my ken at this stage). I searched for other time sheet/time calculation threads and read them and haven't found an answer.
The time sheet is attached.
Sunday is the 95 test of my formula: it works.
Monday is the same formula, dragandcopied, for 7:45 am to 4:30 pm with a 45 minute lunch; it should be 8 hours but the calculated result is 8.25.
Tuesday shows the source of the calculation error: it calculates 7:45 am4:30 pm as 9 hours with lunch removed from the equation.
Wednesday is the same formula as Tuesday minus zero lunch  again, 9 hours.
Thursday is the formula HR provided, which has me paying them. I like my new job, but not that much.
All I really need is getting "Monday" to work. Being told what caused the problem(s) (in my formula and/or HR's formula, or other causes) would be a lovely bonus! The other days are included in the hope that they provide clues to mavens.
Thanks in advance!

20090607, 04:17 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Welcome to the Lounge!
The problem in your formula is that you use HOUR(...) which returns the hour of the Begin Time or End Time, completely ignoring the minutes..
The problem in the original formula is that it combines times such as 9:30 and decimal hours such as 0.75 without taking into account that they use different units.
Use the following formula in F16:
=24*(E16C16)D16
and copy down. If Excel formats the result as a time, format it as a number with 2 decimal places instead.
Explanation: Excel stores dates and times with 1 day (24 hours) as unit. For example 6 AM = 6/24 = 0.25, 12 noon = 12/24 = 0.50, and 6 PM = 18/24 = 0.75. To convert a time difference to a decimal number of hours, you must multiply it with 24, the number of hours in a day. Hence the 24*(E16C16). You don't have to multiply the lunch time D16 with 24 since it's already entered as a decimal time.

20090607, 10:06 #3
 Join Date
 Jun 2009
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you, HansV! I understand where I went wrong, and the formula works great if I put it in Column G. However, when I put it in Column F (where it needs to be) it returns "FALSE" even though the formulas are identical. (file attached) I don't see any IF statements or lookups in Column F, and the compatibility checker is unchecked. (Previous threads discussed those things in relation to unwanted "FALSE" results, so I explored the possibilities as best I could.) I haven't tried any macros or VBA, but who knows what the original author did...
It's in Excel 2007, by the way. Sorry I forgot to mention that before.
Thanks again in advance...

20090607, 10:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Hi,
Since your attachment has extension .xlsxc, it was clear that you're using Excel 2007.
If you select cell F16 and look at the formula bar, you'll see that you have copied the formula more than once:
=24*(E16C16)D16
=24*(E16C16)D16
=24*(E16C16)D16
=24*(E16C16)D16
This causes the problem. You should just use
=24*(E16C16)D16
See the attached version: [attachment=84152:Timeshee...empt1_1_.xlsx]

20090610, 13:22 #5
 Join Date
 Jun 2009
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
I feel foolish but very, very happy. Thank you again, HansV  I'm going to pass this along to our HR department, so you've probably just made life easier for a lot of people who work here.