total man hour in a month

[Laxieryuah]

plese help me to solve this.
[TABLE="width: 566"]
<colgroup><col><col><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 2"] NAME :[/TD]
[TD="colspan: 2"]Bilal[/TD]
[TD] [/TD]
[TD] MONTH:[/TD]
[TD="colspan: 2"]Oct-17[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] DESIGNATION :[/TD]
[TD="colspan: 2"]General Helper[/TD]
[TD] [/TD]
[TD] WEEK NO:[/TD]
[TD="colspan: 2"]WEEK 40/ 41 / 42 /43[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 8"] [/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]DATE[/TD]
[TD]TIME-IN[/TD]
[TD]LUNCH-IN[/TD]
[TD="colspan: 2"]LUNCH-OUT[/TD]
[TD]TIME-OUT[/TD]
[TD]T.W.H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1-Oct-2017[/TD]
[TD]6:55 AM[/TD]
[TD]12:30 PM[/TD]
[TD="colspan: 2"]1:30 PM[/TD]
[TD]4:00 PM[/TD]
[TD]8:05[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2-Oct-2017[/TD]
[TD]6:45 AM[/TD]
[TD]12:30 PM[/TD]
[TD="colspan: 2"]1:30 PM[/TD]
[TD]4:00 PM[/TD]
[TD]8:15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3-Oct-2017[/TD]
[TD]6:45 AM[/TD]
[TD]12:30 PM[/TD]
[TD="colspan: 2"]1:30 PM[/TD]
[TD]4:00 PM[/TD]
[TD]8:15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4-Oct-2017[/TD]
[TD]7:00 AM[/TD]
[TD]12:30 PM[/TD]
[TD="colspan: 2"]1:30 PM[/TD]
[TD]4:00 PM[/TD]
[TD]8:00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5-Oct-2017[/TD]
[TD]6:52 AM[/TD]
[TD]12:30 PM[/TD]
[TD="colspan: 2"]1:30 PM[/TD]
[TD]3:50 PM[/TD]
[TD]7:58
[/TD]
[/TR]
</tbody>[/TABLE]

I want to add T.W.H mean total man hrs.supposedly this data until Oct 31.
so i used formula of =IF(I9=16,"",IF(I9>7,"U","8"))*1 to get the total man hrs per day
but it keep on showing " #value !. on the other hand on day 1 & 2 the formula are working but
on the suceeding date it show the error.

 

[Laxieryuah]

i cannot edit the formula it keep on breaking.
here the formula what i mention.
=SUM(IF(E13<D13,E13+1,E13)-D13,IF(H13<F13,H13+1,H13)-F13)

 

[oldbrewer]

8:16 8:35 ?????

 

[oldbrewer]

put spaces around " < " and " > "

 

[Laxieryuah]

=SUM(IF(E11 < D11,E11+1,E11)-D11,IF(H11 < F11,H11+1,H11)-F11) here's the formula i'm using to get the TWH per day.
please refer below given time.
Oct 1 8:15 = 8:00
Oct 2 7:50 = 8:00
oct 3 absent= 23:00
so anyone can give better formula to get the exact value im looking for, and for the final result every end of the month
i want to get the total hours w/o the adding those value of 23:00, what the formula do i need to be use.

 

[oldbrewer]

count the working hours if they are less than 23:00, sum them if they are less than 23:00 - will post a simple example in an hour or so......

 

[Laxieryuah]

=SUM(IF(E11 < D11,E11+1,E11)-D11,IF(H11 < F11,H11+1,H11)-F11) This is the formula im using to get total working hour per day
so if use this formula it gives me an answer of :
Oct 1 8:15 = but i want to be result like this " 8:00"
Oct 2 7:50 = but i want to be result like this " 8:00 "
oct 3 absent= 23:00

and when im trying to get the final total working hour per month
this is the formula im using : =SUMIF(I50:I79," < 23")*24
but it give me a result of: "39.08" anyone can revised my error in this formula.
i want to make total hours without the value of 23:00,
and make everyday working hour will be fix in 8 hrs.

 

[oldbrewer]

[TABLE="width: 1240"]
<colgroup><col span="4"><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]name[/TD]
[TD]TIME-IN[/TD]
[TD]LUNCH-IN[/TD]
[TD]LUNCH-OUT[/TD]
[TD]TIME-OUT[/TD]
[TD]T.W.H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Oct-17[/TD]
[TD]tom[/TD]
[TD="align: right"]6:55 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Oct-17[/TD]
[TD]fred[/TD]
[TD="align: right"]6:45 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]M1 and N1 are the reference times[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Oct-17[/TD]
[TD]bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-17[/TD]
[TD]tom[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-17[/TD]
[TD]fred[/TD]
[TD="align: right"]6:52 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]3:50 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct-17[/TD]
[TD]bill[/TD]
[TD="align: right"]6:55 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Oct-17[/TD]
[TD]tom[/TD]
[TD="align: right"]6:45 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Oct-17[/TD]
[TD]fred[/TD]
[TD="align: right"]6:55 AM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]2:30 PM[/TD]
[TD="align: right"]4:10 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Oct-17[/TD]
[TD]bill[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Oct-17[/TD]
[TD]tom[/TD]
[TD="align: right"]6:52 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]3:50 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Oct-17[/TD]
[TD]fred[/TD]
[TD="align: right"]6:55 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Oct-17[/TD]
[TD]bill[/TD]
[TD="align: right"]6:45 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Oct-17[/TD]
[TD]tom[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Oct-17[/TD]
[TD]fred[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Oct-17[/TD]
[TD]bill[/TD]
[TD="align: right"]6:52 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"]3:50 PM[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=IF(AND(C2="",D2="",E2="",F2=""),$M$1,$N$1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]so however many hours a person works they are only credited with 8 hours ???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]tom[/TD]
[TD="align: right"]40[/TD]
[TD="colspan: 2"]##########[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]fred[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bill[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]##########[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($B$2:$B$16=C26)*($G$2:$G$16<>$M$1))*8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Laxieryuah]

WOWOWOOOW this the answer i'm looking for many thanks sir.
you save me from this. yes even they work more than 8 hrs it will count only
for 8 hrs.
again many thanks to you.

 

[oldbrewer]

I just like helping people and getting my 72 year old brain into second gear....