|
|
| Re: Excel CORRECTION (1457253) | |||
 |
|||
| Home > SubChat | |||
|
[ Read Responses | Post a New Response | Return to the Index ] |
|
||
|
Re: Excel CORRECTION |
|
|
Posted by Bill West on Wed Dec 6 17:04:04 2017, in response to Re: Excel CORRECTION, posted by BusRider on Wed Dec 6 11:46:41 2017. A style point, (C5-8) reads the 8 hours as days and won't work, C5-"8:00" and C5-1/3 read it as hours. We have to decide whether to stay in days/decimal hours format which involve "8:00" or 1/3 divisors and gives answers such as 2:20 PM for 14 1/3 hours. Or we can multiply by 24 and count in hours, but then we get 14.333. Perhaps the best alternative would be to use a custom cell format of h:mm to show the answer as 14:20.I take it we are talking about a labour contract that has a minimum of 8 hours pay for a day. C7=IF(C5>0,"8:00",0) will show the straight time pay for working at all that day. =MAX(C5,"8:00") will show the greater of the actual hours and the minimum hours to be paid for. C8=MAX((C5-"8:00"),0) or C8=MAX((C5-C7),0) will show the overtime portion of the hours worked with any negative values in (C5-"8:00") suppressed. For the C8 failure the Formulas tab, Evaluate Formula/Evaluate (keep clicking slowly, ok to go around again) shows that MROUND(C5-"8:00",.... fails when it has negative values such as 7:55 hours minus 8 hours. Apparently MROUND wasn't written to do mixed plusses and minuses. An alternative, ROUND((C5-"8:00"/"0:15",0)*( "0:15") will work but is awkward. With my preceding suggestion this is not a problem anyway because C8=MROUND(MAX((C5-"8:00"),0),"0:15")*24 avoids the negative numbers. As a labour contract I'm wondering if you are meaning all overtime is paid in 15 minute increments, more exactly rounded UP to 15 minute increments. If so the CEILING function is what we need here instead of MROUND. The syntax is the same but be careful that for the case of -6 minutes and an increment of +15 minutes that the next ceiling point is upwards is at zero minutes not down at -15. Apple Numbers and Google Sheets (sign in required) have free up to date spreadsheets as alternatives. As an aside, I had first thought you were looking at timetables. A little trick with them that would still apply to evening shift timesheets involves going past midnight. We all recognize that 10 PM to 2 AM is 4 hours but Excel can be literal and think that it is 2AM to 10PM which is 20 hours or that 2 AM minus 10 PM is negative and call it an ERROR. So even if a schedule is valid every day you have to record somewhere that the main service it is on Day 1 and the late night service is Day 2. Then adding the day number to any calculations will avoid trouble. I once ran a series of distance/speed/ time calculations that covered 5 days. I got caught by recording a stop on day 3 as simply being 1:00 PM to 3:00 PM and resuming the running tally from there. It of course lost the 48 hours that had accumulated from day 1. I had to faithfully include the day number with all entries to keep them working. I put a 1.0 width column in at the left to carry the day+time while keeping a date heading and just the time of day in the block of cells that discussed a particular day. The day heading and time entry in them were displayed as equal to that narrow column but each with its own display format. Bill |
|
|
|