Results 1 to 6 of 6
Thread: Timesheet start and end formula

20150121, 10:29 #1
 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.

20150121, 11:20 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,444
 Thanks
 166
 Thanked 651 Times in 619 Posts
Hi
welcome to the lounge!
see attached
zeddy

20150121, 14:19 #3
 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.

20150121, 14:25 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,016
 Thanks
 423
 Thanked 1,608 Times in 1,452 Posts
Mlynn,
As originally written the formula caps the hours at 8! e.g. =If(.......>8,8,......)
HTHLast edited by RetiredGeek; 20150121 at 14:41.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150121, 14:37 #5
 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!!!!!

20150122, 03:55 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,444
 Thanks
 166
 Thanked 651 Times in 619 Posts
Hi
Instead of using the long formula..
=IF(((D9C9)+(F9E9)+(H9G9)+(J9I9)+(L9K9))*24>8,8,((D9C9)+(F9E9)+(H9G9)+(J9I9)+(L9K9))*24)
..you can use this shorter formula:
=MIN(((D9C9)+(F9E9)+(H9G9)+(J9I9)+(L9K9))*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

The Following User Says Thank You to zeddy For This Useful Post:
RetiredGeek (20150122)