Thread: Timesheet start and end formula

1. 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.

2. Hi

welcome to the lounge!

see attached

zeddy

3. 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. Mlynn,

As originally written the formula caps the hours at 8! e.g. =If(.......>8,8,......)
HTH

5. Of course!!! Oh my goodness, long day!!! Time for me to leave and hopefully brain will be working better tomorrow THANK YOU!!!!!

6. 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

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
•