Need help with formula.

J

Jerzydon

New Member
Joined
Jul 26, 2018
Messages
9
For the table below, I need formula that will calculate the number of straight Time Hours btw the clock in and clock out times, but I also need it to add an hour for everything break hour that is worked through. I tried a combination of conjunctive IF's with AND/OR with no success. Any help is greatly appreciated.
















[FONT=&quot]8:00[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]16:00[/FONT]​
[FONT=&quot]7[/FONT]​
[FONT=&quot]19:00[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]Yes[/FONT]​
[FONT=&quot]3:00[/FONT]​
[FONT=&quot]7[/FONT]​
[FONT=&quot]8:00[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]Yes[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]15:00[/FONT]​
[FONT=&quot]6[/FONT]​
[FONT=&quot]7:00[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]Yes[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]19:00[/FONT]​
[FONT=&quot]11[/FONT]​
[FONT=&quot]7:00[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]Yes[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]No[/FONT]​
[FONT=&quot]16:00[/FONT]​
[FONT=&quot]8[/FONT]​


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





<tbody>
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Date[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Clock-In[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Break 1[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Break 2[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Break 3[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Break 4[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Clock-Out[/FONT]​
[/TD]
[TD="bgcolor: #bec0bf"]
[FONT=&quot]Straight 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: #dcdcdc"]
[FONT=&quot]Jul 16, 2018[/FONT]​
[/TD]

[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 17, 2018[/FONT]​
[/TD]
[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 18, 2018[/FONT]​
[/TD]
[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 19, 2018[/FONT]​
[/TD]
[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 20, 2018[/FONT]​
[/TD]
[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 21, 2018[/FONT]​
[/TD]
[TD="bgcolor: #dcdcdc"]
[FONT=&quot]Jul 22, 2018[/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]39[/FONT]​
[/TD]

</tbody>

 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Thank you! As a follow up, I also would like to use a formula to calculate hours between 8am-12pm & 1pm-5pm as straight time;1am-5am, 7am-8am & 7pm-12am as time and a half; and 6am-7am, 12pm-1pm, 6pm-7pm and 12am-1am as double time; all without needing to have the fields manually input and their own respective cells?
 
Upvote 0
I don't know what straight time, double time, or half is but if you give me some examples, I might be able to come up with something.
 
Upvote 0
I have clolumns after the clock out time that 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.

Straight Time is any hours worked between 8am-12pm, and 1pm-5pm.
Time & 1/2 is any hours worked between 7am-8am, 5pm-6pm, 7pm-12pm, & 1am-6am.
Double Time is any hours worked during those breaks which are 6am-7am, 12pm-1pm, 6pm-7pm and 12am-1am.

Right now 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.
 
Upvote 0
That is a good explanation. However, if you could give a table like in the original post showing in and out times in different period and the expected outcomes, that would be great.
 
Upvote 0


[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]​

<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]

</tbody>

 
Upvote 0
I have few questions:

1. In the first example (8:00 to 16:00), why is the noon meal time No?
2. In the third example (7:00 to 19:00), why is the noon meal time No?
 
Upvote 0
I a no because the break was taken. The only time it will be yes is if someone works through a mealtime, and that would be a manual input.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
401
ChetanPuri
C
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
567
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
527
shepcs
S
C
  • Question Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
241
ChetanPuri
C
J
  • Question Question
Shift bands
2 3 4
Replies
31
Views
2K
Jwinssss
J

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top