Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry

    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 9-5 work day, but not for a 7:45-4: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 9-5 test of my formula: it works.
    Monday is the same formula, drag-and-copied, 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 am-4: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!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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*(E16-C16)-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*(E16-C16). You don't have to multiply the lunch time D16 with 24 since it's already entered as a decimal time.

  3. #3
    New Lounger
    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...
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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*(E16-C16)-D16
    =24*(E16-C16)-D16
    =24*(E16-C16)-D16
    =24*(E16-C16)-D16

    This causes the problem. You should just use

    =24*(E16-C16)-D16

    See the attached version: [attachment=84152:Timeshee...empt1_1_.xlsx]
    Attached Files Attached Files

  5. #5
    New Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •