How do i count hours between 2 times based on hours in another cell

[MarkDShark]

A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.

example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.

If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also. How can this be done?

 

[Scott Huish]

Why do you need a formula in A4 if you already have the result you want in A1?

 

[MarkDShark]

I need A1 to match A4 after the formula compensates for the 30 minutes lunch. All A1 is, is an entry point where a person would put down how many hours worked. I also need A-4 to recognize when A-1 is more than 6hrs worked, example, 6.5 would be only 6 excluding the 30 min lunch break. It's part of time keeping that i'm trying to capture. I need it to excude the 30 minute lunch if a person works more than 6hrs. Anything 6 and under, the person doesn't get a 30 minute lunch so the hours are more direct. I hope this is a little clearer.

In all i need A4 to recognize greater and less than 6 with a 30 minute lunch being excluded if the hours are more than 6. The 30 minute lunch if takin, is a non work status. All i need to capture is the direct work hours based on the entry in A1.

 

[Yogi Anand]

Hi MarkDShark:

Welcome to MrExcel Board!

How about trying the following formula in cell A4 ...

=A3-A2-((A3-A2)>0.25)*1/48

 

[MarkDShark]

In this scenerio, i need A4 to equal 10 after compensating for the 30 minute lunch break deducted. Otherwise the hours will show 10hrs and 30mins. Plus i also need to formula in A4 to regonize when A1 is over 6hrs. That would be the indications that a 30min lunch must be deducted from the end result. I can get as far as getting the formula to count the hrs and mins between the 2 times, but i can't get it to recognize the more than 6 issue and the lunch break. The only input fields on my scenerio, would be the 3 first cells. A4 would be locked. A1 would be the catalyst based on what a person enters there.

 

[MarkDShark]

<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=top width=118>

in​

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #40ff40; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=118>

out​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #40ff40; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=118>

hrs​

</TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #40ff40; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=118>

(A1) 10:30am ​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #40ff40; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #40ff40; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=118>

(A2) 9:00pm​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #40ff40; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #40ff40; WIDTH: 88.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=118>

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>(A3) </o>​

</TD></TR></TBODY></TABLE>
<o> </o>
<o> </o>
How about this then. The time frames being entered first, A1,A2, which produces A3 total in hours worked minus the 30 minute lunch break which is mandatory after 6hrs worked, but not included in the direct hours worked.

Otherwise A3 will result in 10hrs 30mins where as the 30min is not payable since it’s not direct work time. The time frames would still need to recognize the more than 6hr worked issue. Again after 6hrs on work, the lunch is manadtory but not payable in the direct hours (A3)

 

[Yogi Anand]

Hi MarkDShark:

Did you try the formula I posted? How about rying that in cell A4 and let me know whether that does what you want or not.

 

[MarkDShark]

Oh sorry. yes i did try it, but yet it does not compensate in the 30min lunch or the 6hr issue i'm having. Did you see my new scenerio to your previous response. Maybe thatone will be a little easier to create a formula for. Thanks for your reply!

 

[MarkDShark]

Ok Yogi. I treid your formula once again but with the new scenerio i created. It worked like a charm! I think the other scenerio wasn't allowing what i was trying to do in A4. Thanks for your help! I enjoyed this place i will seek assistance again when needed. Thanks again!

 

[Yogi Anand]

You Are Very Welcome MarkDShark ... now let us keep EXCELing!