Hi guys,
I'm new to the forum, so a shot introduction is in place I suppose. I'm from Belgium, 34 years old and using excel 2014. I always thought I understood excel pretty well, though it seems I'm not always right.:-s
The reason for registering is of course because I've got a question. :-)
I'm calculating time in a sheet for workers on the floor and working time. All times are rounded down to the lowest 15 mins. Ie if a person works from 07:00 to 17:00 (1 hour noon break): he has worked 8 hours. If he stops at 17:14, the result is the same, but if he works to 17:16, the total worktime will show 8:15 worked. So far, no problems.
Workers can however work more than 8 hours (with a max of 9) or less, with a minimum of 6 hours. These "sliding" times need to be registered. I've got no issue with registering the extra times, but when calculating the times which show a negative time (ex. someone has worked 6h45), the result isn't coming in.
My sheet shows times when they batch in in the morning and the time when they batch out in the evening, the outcome can be 1 of following:
1. the outcome is higher than 1 hour: this should give 1 hour (this one is solved) as no more than 1 extra hour can be registered
2. the outcome is lower than 2 hours: this should give -2 hours (this is also solved) as no ore than -2 "extra" hours can be registered
3. The outcome is between +1 and -2 hours: this should give the extra time (= total worked time - 8 hours standard working time) rounded down to the lowest 15 mins.
Currently the formula I've got is: =IF(TIME(HOUR(H75),FLOOR(MINUTE(H75),15),0)>$AA$71,$AA$71,IF(TIME(HOUR(H75),FLOOR(MINUTE(H75),15),0)<$Z$72,$Z$72,TIME(HOUR(H75),FLOOR(MINUTE(H75),15),0)))
H75 shows the wroked time at the end of the day
AA71 shows 01:00 (to refer to the max extra time to be registered)
Z72 shows -02:00 (to refer to the min "extra" time to be registered
This formula works fine as long as the outcome isn't between -2:00 and 00:00. The I get a #Number error.
Hopefully this is still clear for all, but if any questions for inclarities,s please state same.
Thanks in advance for your expertise.
Regards,
Bookmarks