Formula Help

[Jerzydon]

[FONT=&quot]I have a spreadsheet that I’m using to calculate hours worked. column headings are ”Clock In”, “Meal Hour 1”, “Meal Hour 2”, “Meal Hour 3”, ”Meal Hour 4“ & “Clock Out”. After the clock out time there are Stright Time Hours Worked, Time & 1/2 Hours Worked, and Doubletime Hours worked. I want to use a formula that will automatically calculate the correct number of hours for each heading. [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Straight Time are any hours worked between 8am-12pm, and 1pm-5pm. [/FONT]
[FONT=&quot]Time & 1/2 are any hours worked between 7am-8am, 5pm-6pm, 7pm-12pm, & 1am-6am. [/FONT]
[FONT=&quot]Double Time are any hours worked during those breaks which are 6am-7am, 12pm-1pm, 6pm-7pm and 12am-1am. [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Right now I manually calculate the hours for each and fill it in but I would like to automate that process if at all possible using the clock in and clock out fields. I’ve tried conjunctive IF statements and AND/OR but I’ve had no luck. Can anyone point me in the right direction?[/FONT]

 

[RasGhul]

Hi

It would help posters develop a solution if they can see sample data in your sheet layout.

 

[Jerzydon]

Saturday and Sunday are all overtime hours as well.

[FONT=&quot]8:00 AM[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]4:00 PM[/FONT]​	[FONT=&quot]7[/FONT]​	[FONT=&quot]0[/FONT]​	[FONT=&quot]0[/FONT]​
[FONT=&quot]8:00 AM[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]Yes[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]3:00 PM[/FONT]​	[FONT=&quot]6[/FONT]​	[FONT=&quot]0[/FONT]​	[FONT=&quot]1[/FONT]​
[FONT=&quot]7:00 AM[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]Yes[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]4:00 PM[/FONT]​	[FONT=&quot]0[/FONT]​	[FONT=&quot]9h[/FONT]​	[FONT=&quot]1[/FONT]​
[FONT=&quot]7:00 AM[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]4:00 PM[/FONT]​	[FONT=&quot]8[/FONT]​	[FONT=&quot]1[/FONT]​	[FONT=&quot]0[/FONT]​
[FONT=&quot]8:00 AM[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]No[/FONT]​	[FONT=&quot]3:00 PM[/FONT]​	[FONT=&quot]6[/FONT]​	[FONT=&quot]0[/FONT]​	[FONT=&quot]0[/FONT]​
						[FONT=&quot]0[/FONT]​	[FONT=&quot]0[/FONT]​	[FONT=&quot]0[/FONT]​

<tbody>
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Date[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Clock-In[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Worked Meal Hour 06-0700[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Worked Meal Hour 12-1300[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Worked Meal Hour 18-1900[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Worked Meal Hour 00-0100[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Clock-Out[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Straight Time Hours Worked[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Time & 1/2 Hours Worked[/FONT]​

[/TD]
[TD="bgcolor: #bec0bf"]

[FONT=&quot]Double Time Hours Worked[/FONT]​

[/TD]

[TD="bgcolor: #61d836"]

[FONT=&quot]Week 1 Start[/FONT]​

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 16, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 17, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 18, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]Yes[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]3:00 AM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 19, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 20, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7:00 AM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]Yes[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]8[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]2[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 21, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 22, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]

[TD="bgcolor: #ee220c"]

[FONT=&quot]Week 1 End[/FONT]​

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[FONT=&quot]21[/FONT]​

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[FONT=&quot]4[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]

[TD="bgcolor: #61d836"]

[FONT=&quot]Week 2 Start[/FONT]​

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]
[TD="bgcolor: #61d836"]

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 23, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7:00 AM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]Yes[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]4:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]7[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 24, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 25, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]8:00 AM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]Yes[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]11:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]8[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]5[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 26, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 27, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]1:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]No[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]5:00 PM[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]4[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]

[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 28, 2018[/FONT]​

[/TD]
[TD="bgcolor: #dcdcdc"]

[FONT=&quot]Jul 29, 2018[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]
[TD="bgcolor: #f5f5f5"]

[FONT=&quot]0[/FONT]​

[/TD]

[TD="bgcolor: #ee220c"]

[FONT=&quot]Week 2 End[/FONT]​

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[/TD]
[TD="bgcolor: #ee220c"]

[FONT=&quot]33[/FONT]​

[/TD]
[TD="bgcolor: #ee220c"]

[FONT=&quot]7[/FONT]​

[/TD]
[TD="bgcolor: #ee220c"]

[FONT=&quot]2[/FONT]​

[/TD]

</tbody>

 

[yky]

In the first table, there is a 9h. Is that a mistake? Should that row be 7 1 1.
From 7 am to 4 pm, how can there be 8 straight hours? Shouldn't it be only 7?

If I am right, I have got a solution for you.

In an obscure area of the spreadsheet, create a range like the following:

[TABLE="width: 192"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]b[/TD]
[/TR]
</tbody>[/TABLE]

The above is only part of the table. 1 is 1 am, 2 is 2 am and so on. I don't know you work hour range. Judging from you example, I created a 32 hour table. from 1 am to 8 am of the next day. If the work hour spans more than that, you create a bigger table., 48 hours, maybe. Here, a is straight hour, b is half, and c is double. The first two columns are for illustration purpose only. You don't actually need them.

Now, you only need to count the number of a and b in the working period. C is entered manually. So, just count "Yes" in column C, D, E, and F.

To count C, use the formula "=COUNTIF(C4:F4,"=Yes")".

To count a, use the formula "=COUNTIF(INDEX($R$1:$R$32, $B4*24):INDEX($R$1:$R$32,IF($G4<$B4, $G4*24+23,$G4*24-1)), "a")".

To count b, change the "a" to "b".

R is the column where a, b, and c reside. If your table is in a different place, you need to change R to suit your need.

 

[Jerzydon]

The 9 would be correct because Saturday and Sunday are all Time and 1/2 and the meal hours are Double. There are no straight Time Hours on the weekend.

 

[yky]

That adds a whole new level of complexity. It's no too difficult to solve except when there are works spanning from Friday to Saturday or Sunday to Monday. Are such cases possible?

 

[Jerzydon]

It is possible. There’s no set timeframe. It’s a “work until finished” schedule so it can go on for any amount of time until the workload isn’t complete. Ikniw there is a formula that can be used to give the correct time when a day runs into another one, I’m just not sure how to structure the combination of formulas to give me the proper result.

I do do appreciate all of the input and assistance.

 

[yky]

Doesn't labor law dictate how long a shift can be? What is the longest shift? Judging from what you said, there are four scenarios here, Friday, Saturday, Sunday, and Monday to Thursday. The formula will be quite long. I'm trying to see if there is a way to simplify it. For example, if work starting on Sunday will not exceed 8 AM on Monday, then, Saturday and Sunday collapse into one scenario, not two. So, there are only three scenarios to consider.

You said there is a formula to give the correct hours when shift spans over midnight. What is the formula?