I have a by-weekly time card where the guys some take a lunch and others do not. I have a formula that will figure hours worked for the days I know they are working 12+ hours "=IFERROR(MIN(IF(MEDIAN((D25-C25+(C25>D25)),"07:44","08:16")=(D25-C25+(C25>D25)),"12:00",(D25-C25+(C25>D25))),"12:00")*24,0)". The problem is when I get between 8 - 12 hours it automatically goes to 12 hours work even if they didn't work 12. Than I want column L to sum K24-K30 and when it gets to 40 to put the time in OT side and the same for K31-K37. Out work week goes from Sun - Sat. Right now my formula calculates OT for each day over certain number of hours. Is this possible or do I need to just keep typing in manually? File attached for review.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Calculate hours worked w/lunch & w/o lunch, than OT over 40 hours in week
0
Answers
0
Here are the two fomulas you need.
[K25] =MIN(SUM(($D25-$C25),($F25-$E25),($H25-$G25),($J25-$I25)),0.5)and
[L25] =MAX(SUM(($D25-$C25),($F25-$E25),($H25-$G25),($J25-$I25))-0.5,0)K25 will show the sum of all the times worked to a maximum of 12 while L25 will show the whatever is in addition to 12 based on the same sum. In both formulas 0.5 represents half a day = 12 hours. Make sure that both cells have the custom NumberFormat hh:mm so that they can display times..
Your formula, mistaken though it appears in its use of the MEDIAN() function, seems to want to take account of worktime ending on the next day. This is something the above formulas don't do. In order to do that you would need to change the way you enter times in Sheet1. I urge you to read up on how Excel records and displays times and dates.
Start with reformatting Sheet1!A3 with the custom NumberFormat "d/m" (enter without quotation marks). Then, when you enter 2/4 in the cell Excel will record 2.4.2018, adding the current year automatically. If you need 2.4.2017 you must specify the year in your entry. Due to the format, either entry will display "2/4". In A4 enter =$A3+1 and copy down.
Next format Sheet1!B3 the the custom NumberFormat "ddd" (no quotation marks) and enter the formula =$A3. Now this cell will have the exact same date (2.4.2017) as its neighbour in column A but will display "Sun". Cope the formula down as required.
The formula [Template!A3] =Sheet1$A3 will now import a proper date. You will need to apply the same format as on Sheet1. Repeat with [Template!B3] =Sheet1$B3 and copy both cells down as required.
[Template!A24] =Sheet1$A3 is better than [Template!A24] =$A3 for purposes of maintenance. You want to know where the value comes from. However, the effect of the whole exercise is to know which date is referred to in the IN and OUT columns C:J, and that would give you the capability to modify calculations like ($D25-$C25) to give the correct result if C25 is on the next day.
Discussion
I changed all the formatting as suggested and in K25 on the template it totals the number of hours correctly now. I entered data in for Mon - Thurs to make sure the formula's were working and it doesn't calulate in the OT column after 40 hours. Also on the bottom in row K38, the total number of hours isn't adding up correctly. What am I maybe doing wrong?
blinhart (rep: 2) Feb 6, '18 at 9:11 am
K38:L38 aren't formatted as Time. Format these cells as hh:mm to have the correct result.
As for limiting the weekly work hours to 40, this is a different question which doesn't belong in this thread. However I can tell you this much here: if you want a weekly account don't create a fourtnighty one. You can construct a hugely complicated formula which will do what you want but your "report" will be so opaque that it doesn't deserve that title anymore.
If your form would have one box per week the total (say K38) can be limited to 40 using the Min() function just as in K25. In L38 you can show the sum of the OT in column L + the overhang from column K using the Max() function just as shown in L25.
As for limiting the weekly work hours to 40, this is a different question which doesn't belong in this thread. However I can tell you this much here: if you want a weekly account don't create a fourtnighty one. You can construct a hugely complicated formula which will do what you want but your "report" will be so opaque that it doesn't deserve that title anymore.
If your form would have one box per week the total (say K38) can be limited to 40 using the Min() function just as in K25. In L38 you can show the sum of the OT in column L + the overhang from column K using the Max() function just as shown in L25.
Variatus (rep: 4889) Feb 6, '18 at 9:57 am
How did you make out? Do you still need that "hugely complicated formula" you last thought about? Did you adjust your form to show weekly totals?
Variatus (rep: 4889) Feb 8, '18 at 5:20 am