total man hour in a month

[Laxieryuah]

plese help me to solve this.

NAME :	Bilal		MONTH:	Oct-17
DESIGNATION :	General Helper		WEEK NO:	WEEK 40/ 41 / 42 /43
NO	DATE	TIME-IN	LUNCH-IN	LUNCH-OUT	TIME-OUT	T.W.H
1	1-Oct-2017	6:55 AM	12:30 PM	1:30 PM	4:00 PM	8:05
2	2-Oct-2017	6:45 AM	12:30 PM	1:30 PM	4:00 PM	8:15
3	3-Oct-2017	6:45 AM	12:30 PM	1:30 PM	4:00 PM	8:15
4	4-Oct-2017	7:00 AM	12:30 PM	1:30 PM	4:00 PM	8:00
5	5-Oct-2017	6:52 AM	12:30 PM	1:30 PM	3:50 PM	7:58

<colgroup><col><col><col span="2"><col><col span="2"><col></colgroup><tbody>
</tbody>

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]

DATE

name

TIME-IN

LUNCH-IN

LUNCH-OUT

TIME-OUT

T.W.H

23:00

08:00

01-Oct-17

tom

6:55 AM

12:30 PM

1:30 PM

4:00 PM

08:00

#####

01-Oct-17

fred

6:45 AM

12:30 PM

1:30 PM

4:00 PM

08:00

M1 and N1 are the reference times

01-Oct-17

bill

23:00

02-Oct-17

tom

7:00 AM

12:30 PM

1:30 PM

4:00 PM

08:00

02-Oct-17

fred

6:52 AM

12:30 PM

1:30 PM

3:50 PM

08:00

02-Oct-17

bill

6:55 AM

12:30 PM

1:30 PM

4:00 PM

08:00

03-Oct-17

tom

6:45 AM

12:30 PM

1:30 PM

4:00 PM

08:00

03-Oct-17

fred

6:55 AM

1:30 PM

2:30 PM

4:10 PM

08:00

03-Oct-17

bill

7:00 AM

12:30 PM

1:30 PM

4:00 PM

08:00

04-Oct-17

tom

6:52 AM

12:30 PM

1:30 PM

3:50 PM

08:00

04-Oct-17

fred

6:55 AM

12:30 PM

1:30 PM

4:00 PM

08:00

04-Oct-17

bill

6:45 AM

12:30 PM

1:30 PM

4:00 PM

08:00

05-Oct-17

tom

7:00 AM

12:30 PM

1:30 PM

4:00 PM

08:00

05-Oct-17

fred

7:00 AM

12:30 PM

1:30 PM

4:00 PM

08:00

05-Oct-17

bill

6:52 AM

12:30 PM

1:30 PM

3:50 PM

08:00

#####

=IF(AND(C2="",D2="",E2="",F2=""),$M$1,$N$1)

so however many hours a person works they are only credited with 8 hours ???

tom

40

##########

fred

40

bill

32

##########

=SUMPRODUCT(($B$2:$B$16=C26)*($G$2:$G$16<>$M$1))*8

<colgroup><col span="4"><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[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....