|
|
| Excel (1457154) | |
 |
|
| Home > SubChat | |
[ Post a New Response | Return to the Index ]
|
| (1457154) | |
Excel |
|
|
Posted by BusRider on Tue Dec 5 23:00:41 2017 By chance, is there anybody here that is proficient in Excel, that would be willing to assist me with IF formulas by using time? |
|
|
| (1457162) | |
Re: Excel |
|
|
Posted by Bill West on Wed Dec 6 03:35:16 2017, in response to Excel, posted by BusRider on Tue Dec 5 23:00:41 2017. I'll give it a go. While dates are displayed in a readable format they are processed as a day number starting from 1900 or 1904 and time as a decimal fraction of a 24 hour day. Formatting a date or time cell as a decimal number will reveal this. Make sure the column width is enough when you are looking in date format.So an IF formula will compare the dates as if they are just a number. If you are comparing a date with a time against another you need to sum the 2 parts for the IF statement. December 6, 2017 and 6am added together is 43075.25 and December 7, 2017 9pm is 43076.75. IF((D1+T1)<(D2+T2),"is earlier","is later") will work. If that's not enough, post your problem. Bill |
|
|
| (1457169) | |
Re: Excel |
|
|
Posted by AlM on Wed Dec 6 08:12:21 2017, in response to Re: Excel, posted by Bill West on Wed Dec 6 03:35:16 2017. One more helpful hint: you can write a time of day as, for example, 7:52:30 PM. If you want to know its decimal value, then in another cell write =VALUE(that previous cell). If your new cell is formatted in decimal, which cells are by default, it will show 0.828125. |
|
|
(Sponsored) |
iPhone 6 (4.7 Inch) Premium PU Leather Wallet Case - Red w/ Floral Interior - by Notch-It |
|
| (1457174) | |
Re: Excel |
|
|
Posted by BusRider on Wed Dec 6 10:08:10 2017, in response to Re: Excel, posted by Bill West on Wed Dec 6 03:35:16 2017. Thank you Bill and AIM. What I am trying to solve its pretty detailed.Here's Part 1 of 3: In cell B3, I have 13:50 In cell B5, I have 21:45 In cell C5 I have SUM(B5-B3) to equal 7:55 as such. But, in cells C7/C8 to determine Straight-time and Overtime AND to convert it to read 8 (C7) and "blank" (C8) being there is no Overtime. Because, I have not figured out a way for C7 to always display 8 based off of the formula in C5, I just entered 8 in that cell particularly. So in cell C8, I have this formula: "=IF(OR(C5<"8:00",C5>"8:00"),MROUND(C5-"8:00","0:15")*24,0)" Based on this formula, the logical test says its True, but cell C8 reads #NUM!, but when I switch the numbers so it has Overtime, as an example 14:20 start and 23:05 end, C8 reads correctly as 0.75. I played around with the formula as well, also using/including the AND function. If any of you, can assist I would greatly appreciate it. |
|
|
| (1457175) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 10:10:20 2017, in response to Re: Excel, posted by BusRider on Wed Dec 6 10:08:10 2017. Thank you Bill and AIM. What I am trying to solve its pretty detailed.Here's Part 1 of 3: In cell B3, I have 13:50 In cell B5, I have 21:45 In cell C5 I have SUM(B5-B3) to equal 7:55 as such. But, in cells C7/C8 to determine Straight-time and Overtime AND to convert it to read 8 (C7) and "blank" (C8) being there is no Overtime. Because, I have not figured out a way for C7 to always display 8 based off of the formula in C5, I just entered 8 in that cell particularly. So in cell C8, I have this formula: "=IF(OR(C5<"8:00",C5>"8:00"),MROUND(C5-"8:00","0:15")*24,0)" Based on this formula, the logical test says its True, but cell C8 reads #NUM!, but when I switch the numbers so it has Overtime, as an example 14:20 start and 23:05 end, C8 reads correctly as 0.75. I played around with the formula as well, also using/including the AND function. Also, in addition, to convert C5 from reading 0.00 to a blank cell, I followed instructions using Conditional Formatting but it did nothing on my end. If any of you, can assist I would greatly appreciate it. |
|
|
| (1457178) | |
Re: Excel CORRECTION |
|
|
Posted by AlM on Wed Dec 6 10:27:59 2017, in response to Re: Excel CORRECTION, posted by BusRider on Wed Dec 6 10:10:20 2017. I have an old version of Excel that doesn't have MROUND, so I can't quite reproduce what you're doing. But I think everything will work if you use the number 1/3 instead of "8:00" and the number 1/96 instead of "0:15". |
|
|
| (1457180) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 10:44:15 2017, in response to Re: Excel CORRECTION, posted by AlM on Wed Dec 6 10:27:59 2017. I tried it with the regular ROUND function and it did not work. Then, I used the MROUND and changed to 1/3 and 1/96 respectfully and got the same output. So yes, it made the formula a bit simpler. Thank you in that regard. |
|
|
| (1457181) | |
Re: Excel CORRECTION |
|
|
Posted by AlM on Wed Dec 6 10:49:38 2017, in response to Re: Excel CORRECTION, posted by BusRider on Wed Dec 6 10:44:15 2017. got the same outputWait, do you still get #NUM once you replace "8:00" by 1/3? That would surprise me greatly. |
|
|
| (1457184) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 11:46:41 2017, in response to Re: Excel CORRECTION, posted by AlM on Wed Dec 6 10:49:38 2017. Yes, I do. That's why I believe its something with the logical test because if i do the logical test individually I get the correct output no matter what times I enter. |
|
|
| (1457246) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 16:53:33 2017, in response to Re: Excel CORRECTION, posted by AlM on Wed Dec 6 10:49:38 2017. I was able to make the cell blank if the output is 0. Still trying to work on the the IF statement using the OR function as well. |
|
|
| (1457253) | |
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 |
|
|
| (1457281) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 21:02:11 2017, in response to Re: Excel CORRECTION, posted by Bill West on Wed Dec 6 17:04:04 2017. Hey, Thank you for the explanation, I am definitely going to try it in a new spreadsheet. But I figured it out, the OR function was incorrect, I switched it to the AND function swapped the logical tests and it worked perfect and continues to do as so thus far using different times.To piggy back on what your saying your response. Part 3 of what I'm trying to figure out is, writing an IF statement (I assume) to convert into 8 hours. For Example: Say in cell C7 its 8.25 in cell C8 its 0.5 But the actual answer is ST 8 and OT is 0.75. I can do it manually in different cells using basic math and converting. |
|
|
| (1457283) | |
Re: Excel CORRECTION |
|
|
Posted by BusRider on Wed Dec 6 21:13:09 2017, in response to Re: Excel CORRECTION, posted by Bill West on Wed Dec 6 17:04:04 2017. Also, how do you do CEILING? |
|
|
| (1457294) | |
Re: Excel |
|
|
Posted by Bill West on Thu Dec 7 01:03:05 2017, in response to Re: Excel CORRECTION, posted by BusRider on Wed Dec 6 21:13:09 2017. =CEILING(C5,"0:15") -rounds up=ROUND(C5,"0:15") -rounds to nearest =FLOOR(C5,"0:15") -rounds down When you are on a blank cell, clicking the Fx at the left of the formula bar will bring up a list of functions. Choosing one opens a box to guide you through filling it in and the box has a help link with full details on the function. Bill |
|
|
| (1457295) | |
Re: Excel |
|
|
Posted by Bill West on Thu Dec 7 01:26:02 2017, in response to Re: Excel CORRECTION, posted by BusRider on Wed Dec 6 21:02:11 2017. If you mean you now have C8=IF(AND(C5<"8:00",C5>"8:00"),0,MROUND(C5-"8:00","0:15")*24), I think it will still stall when C5 is 7:55. Maybe I'm not correctly following your update.C7=IF(C5>0,"8:00",0) and C8=MAX(C5-"8:00",0) should separate the straight time and overtime. Bill |
|
|
| (1457324) | |
Re: Excel |
|
|
Posted by BusRider on Thu Dec 7 10:27:31 2017, in response to Re: Excel, posted by Bill West on Thu Dec 7 01:03:05 2017. Thank you! |
|
|
| (1457331) | |
Re: Excel |
|
|
Posted by BusRider on Thu Dec 7 10:56:43 2017, in response to Re: Excel, posted by Bill West on Thu Dec 7 01:26:02 2017. Thank you again, So far the first formula still works with the 7:55.The second set of formulas, I will give it a try later. This is the second problem I'm working on: J1 = 5:00 J2 = 5:10 J3 = 12:30 K4=SUM(J3-J1) = 7:30 J5 = 15:10 11th Hour of Work J6=J2+11/24 = 16:10 J7 = 20:00 K6=SUM(J6-J5) = 1:00 K7=SUM(J7-J6) = 3:50 Straight K8=MROUND(SUM(K4,K6),1/96)*24 = 8.5 (7:30 + 1:00 = 8:30) Overtime K9=MROUND(K7,1/96)*24 = 3.75 (3:50) But K8 ST is supposed to be 8 and K9 OT is supposed to be 4.25 |
|
|
| (1457419) | |
Re: Excel |
|
|
Posted by Bill West on Thu Dec 7 22:04:42 2017, in response to Re: Excel, posted by BusRider on Thu Dec 7 10:56:43 2017. Is this a case of overtime starts after 8 hours of working or 11 hours since starting, whichever comes first? If so I think we would need a cell for this: "OT kicks in at"=MIN("time 8hrs is up","time 11hrs is up")And then calculate ST hours up to that cell and OT hours on from it. If there was any real world way for the midday break to occur later than either of those times then we might have to do more with the calculation. Bill |
|
|
| (1457424) | |
Re: Excel |
|
|
Posted by BusRider on Thu Dec 7 22:49:30 2017, in response to Re: Excel, posted by Bill West on Thu Dec 7 22:04:42 2017. That is correct.The times can vary though, its just that anything before the 11th hour is considered ST and after 11 hours is OT but also OT is standard after the 8 hours. So its kind of a combination but I have a feeling this formula is going to get complicated. I wasn't sure if a formula could be created with that many "constraints". |
|
|
| (1457517) | |
Re: Excel |
|
|
Posted by Bill West on Sat Dec 9 02:26:16 2017, in response to Re: Excel, posted by BusRider on Thu Dec 7 22:49:30 2017. Hi, Rider, here's a different approach. I've tried to solve split shifts and the 8 & 11 hours OT rules together. It gets long but the formulas are short and understandable. They could readily be merged into a single cell for each time but would be very hard to follow.row........col E (align right)...................col F.....col G 4 arrival..............................................5:00 5 commence work................................5:10 6 7 start break......................................11:30 (if any) 8 end break.......................................14:10 9 10 finish day........................................20:05 11 12 total day..........................................15:05 =arrival to finish 13 break hours.......................................2:40 =break start to end 14 total work hours...............................12:25 =net of above 15 hours payable as ST under 8hr rule......8:00 =lesser of work hours and 8hrs 16 17 11 hrs on duty occurs at...................16:10 o'clock (or finish time if earlier) 18 total hours to that point....................11:10 =arrival to 11hr time 19 break hours to that point....................2:40 =(earlier of break start and 11hr time) to (earlier of break end and 11hr time) 20 work hours to that point....................8:30 =net of above 21 hours payable as ST under 11hr rule..8:30 =lesser of work hours to 11hr mark and 11hrs 22 23.....................................................8:00 =the ST hours worked under the more limiting rule 24 payroll ST =...................................8:00 =minimum day's pay 25 ....................................................4:25 =rest of the work hours 26 payroll OT =....................................4:30 =rounded up in 15 minute increments The formulas in col F are: They are all custom formatted h:mm . 12 =F10-F4 13 =F8-F7 14 =F12-F13 15 =MIN(F14,"8:00") 16 17 =MIN(F5+"11:00",F10) 18 =F17-F4 19 =MIN(F8,F17)-MIN(F7,F17) 20 =F18-F19 21 =MIN(F20,"11:00") 22 23 =MIN(F21,F15) 24 =MAX(F23,"8:00") 25 =F14-F23 26 =CEILING(F25,"00:15") This also brought out more questions about the contract terms: 1. Try a 6:55 break start, it makes the worked ST low. Would pay still be 8hrs ST then OT just as a premium? 2. In general, for 10hrs worked does the pay stub show 10hrs worked at ST rate and 2hrs of OT premium? Or does it show 8hrs ST and 2hrs OT rate? 3. For 6hrs worked, with half beyond the 11hr rule, does it show 3hrs ST and 3hrs OT? If so how is the 8hr minimum pay for the day shown? Is it 8hrs ST and 3hrs OT rate? 4. If 3hrs ST is indeed shown, would it be rounded up in 15min increments? 5. Is the 10 minutes at the start fixed or variable? Bill |
|
|
| (1457554) | |
Re: Excel |
|
|
Posted by BusRider on Sat Dec 9 13:41:47 2017, in response to Re: Excel, posted by Bill West on Sat Dec 9 02:26:16 2017. Mr. West, Thank you for your efforts with assisting me first and foremost. Being you are helping me, I wanted to give you the information as we go as this is appears a time consuming and complicated task that I'm working on.I changed the formula in cell F26 to "FLOOR" instead of CEILING. Using the four examples that I have in front of me, FLOOR came out with the appropriate OT figure for each example. I also, altered the formulas in both cells F24/26 to include MROUND. Now to throw a wrench in your hard work, the payroll ST in cell F24 is either 7.25, 7.5, 7.75 or 8. With the numbers we used it comes out correct 8 ST 4.25. However when I change the times as example: arrival 6:54am commence work 7:04am start break 11:10am end break 2:34pm finish day 7:35pm The formulas come out to ST 8 OT 1.5 instead of ST 7.75 OT 1.5. Please note as well 7.25 is the lowest so anything below 7h07 would be 7.25 ST. To answer your questions: 1)No, for a 6:55 start it would be 7.25 ST and 3.75 OT. 2)8hrs ST and 2hrs OT rate. 3)If I understood you correctly your saying: arrival 5:00am commence work 5:10am start break 7:50am end break 4:00pm finish day 7:10pm Yes it does show as 3 ST and 3 OT (cells F23/F25). According to your formula it is shown as 8 ST and 3 OT (F24/F26). 4)For the sake of your question, based on the 7.25 pay guarantee for splits and 8 hour work guarantee for all, I would say it would be 7.25 ST and 3 OT. However, operationally speaking in transit, this would not be an actual work shift being its only 5h20 in total. Ideally, I would say both shifts (or pieces) of work would be individual trippers used as additional OT for regular run operators. 5)Fixed, unless its a road relief, then there is none it would be the same time as commence work. |
|
|
| (1457622) | |
Re: Excel |
|
|
Posted by Bill West on Sun Dec 10 02:32:05 2017, in response to Re: Excel, posted by BusRider on Sat Dec 9 13:41:47 2017. You are most welcome.For the rounding, it could be applied to the finish time, the ST calc, the OT calc or the payroll calcs. What it shouldn't be is applied multiple times. If it is applied to ST then in my layout OT will be rounded in the opposite direction. So if the rule is to round up, then CEILING is what we should work with and the problem must be in the layout of my solution. I found an old MTA union contract as a sample to read, this field is complicated. It must be fun for the Agency IT departments to program new contracts when they come out. The clauses are reasonable but making a logical chart to account for all the situations where they apply and sorting out the conflicts between them would be a challenge. Not appreciating all the details is why I imposed an 8 hour minimum day at F15 when in fact there's more to it. So for now I think we've roughed out some concepts for Excel and I'll let you push on with fitting it to the details in the contract you are looking at. If after you have all the rules in place Excel still gives you trouble I'd be happy to jump in again. Bill |
|
|
| (1457665) | |
Re: Excel |
|
|
Posted by BusRider on Sun Dec 10 10:00:42 2017, in response to Re: Excel, posted by Bill West on Sun Dec 10 02:32:05 2017. Duly noted! What year is the MTA contract? What were you referencing Buses or Subways? After I solve this labor contract, I wanted to try it with MTA to enhance my Excel skills. |
|
|
| (1457716) | |
Re: Excel |
|
|
Posted by Bill West on Sun Dec 10 14:10:49 2017, in response to Re: Excel, posted by BusRider on Sun Dec 10 10:00:42 2017. MTA/MaBSTOA - TWU100 2002 pgs 178, 227, 252 (PDF pgs 304, 353, 378) has MTA bus, MaBSTOA bus overtime and MaBSTOA definitions. In Googling that URL I found this page is also available but the PDF stalled on downloading.Bill |
|
|
| (1457898) | |
Re: Excel |
|
|
Posted by BusRider on Mon Dec 11 11:28:38 2017, in response to Re: Excel, posted by Bill West on Sun Dec 10 14:10:49 2017. Thank you for the links. By chance, Do you know how to round to the next minute in Excel? 9h06 is 1h06 over 8h00, Divided by 2 is 0h47 but the cell outputs 0h46. |
|
|
| (1457922) | |
Re: Excel |
|
|
Posted by Bill West on Mon Dec 11 15:31:05 2017, in response to Re: Excel, posted by BusRider on Mon Dec 11 11:28:38 2017. Yes, the division of a number of minutes could leave a small fraction of a minute. If that is to always be rounded up then CEILING(number of minutes,"0:01") will do the trick.This is a general point in spreadsheet design, while the display is rounded the underlying number is always carried to 30 odd decimal places. When you are doing accounting or in this case timekeeping you have to watch for any calculation that could produce a fraction and round it back out in the same direction a person would, so that it doesn't carry any farther. Because some calculations prepare a first result (ST) and then derive a second (OT) from the difference between the first and the input you have to decide which calculation should be done and rounded first because that determines who is going to gain from the rounding and who is going to lose. If the company does it in the opposite order your results won't match. Bill |
|
|
| (1458305) | |
Re: Excel |
|
|
Posted by BusRider on Thu Dec 14 22:11:10 2017, in response to Re: Excel, posted by Bill West on Mon Dec 11 15:31:05 2017. Quick question: Can you put CELING inside an IF statement? |
|
|
| (1458312) | |
Re: Excel |
|
|
Posted by Bill West on Thu Dec 14 23:16:05 2017, in response to Re: Excel, posted by BusRider on Thu Dec 14 22:11:10 2017. Yes, either as part of the IF's test or as part of the action it is to take.=IF(CEILING(B1,"0:15")>11/24,CEILING(B1,"0:15")+"2:00",CEILING(B1,"0:15")) Bill |
|
|
| (1458351) | |
Re: Excel |
|
|
Posted by BusRider on Fri Dec 15 11:09:34 2017, in response to Re: Excel, posted by Bill West on Thu Dec 14 23:16:05 2017. I ask because I have a SUm formula that equals 5:12 but half of it is 2:37, but the cell output reads 2:36 even when I round it to "0:01" in CEILING. When I use different number it comes out correctly rounded to the appropriate minute. |
|
|
| (1458364) | |
Re: Excel |
|
|
Posted by Bill West on Fri Dec 15 13:33:02 2017, in response to Re: Excel, posted by BusRider on Fri Dec 15 11:09:34 2017. Oops, on paper doesn't half of 5 hours and 12 minutes come to 2 hours and 36 minutes exactly?Bill |
|
|
| (1458503) | |
Re: Excel |
|
|
Posted by BusRider on Sat Dec 16 12:20:53 2017, in response to Re: Excel, posted by Bill West on Fri Dec 15 13:33:02 2017. Yes it does, that's why I'm convinced software's can have typos. The answer on my screen has it as 2:37. |
|
|
| (1458518) | |
Re: Excel |
|
|
Posted by Bill West on Sat Dec 16 15:13:44 2017, in response to Re: Excel, posted by BusRider on Sat Dec 16 12:20:53 2017. When you use a CEILING function any tiny carryover in the previous calculations will get rounded up, even if it is too small to display. IE if the previous answer is 2:36.0000001, CEILING will change it to 2:37. Use the Formulas/Evaluate Formula/Evaluate commands to see what is being used at each calculation step.You can also format the cell as a decimal to 10-15 places and widen the column width to see that. Compare it with what you think the time should be in 24hr decimal format. 2:36 AM is 0.108333333333333 and running =CEILING(A1,"0:01") gave me 2:36. The number 0.108333333333334 however gave me 2:37 Bill |
|
|
