Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timesheet start and end formula

    I need some assistance with a spreadsheet. Student workers need to enter several start and end times and calculate for two week timesheet. The initial spreadsheet had three start/end columns which calculated correctly. The user requested two more start/end columns. I tried updating the formula but cannot get it to work. Appreciate any assistance.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    welcome to the lounge!

    see attached

    zeddy
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, what was I missing? I copied the formula and was able to calculate for new rows; however when user tested by changing times she could not get the formula to update. I also tested by changing some of the existing start/end times and total # hours stayed at 8. For example, try changing first start time from 8:00 to 6:00. Total hours would be 10. Thanks again for assistance.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mlynn,

    As originally written the formula caps the hours at 8! e.g. =If(.......>8,8,......)
    HTH
    Last edited by RetiredGeek; 2015-01-21 at 15:41.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Of course!!! Oh my goodness, long day!!! Time for me to leave and hopefully brain will be working better tomorrow THANK YOU!!!!!

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Instead of using the long formula..
    =IF(((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9))*24>8,8,((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9))*24)

    ..you can use this shorter formula:
    =MIN(((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9))*24,8)

    see attached workbook.

    Essentially, this is
    =MIN(hours,8)

    If hours=0, the formula returns 0 (e.g. for 'empty rows)
    If hours = 6, the formula returns 6 (since this is 'smaller' than 8)
    If hours = 10, the formula returns 8 (sin 8 is smaller than 10, i.e. 8 is the maximum hours permitted)

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-01-22)

Posting Permissions

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