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.

 

[oldbrewer]

what is I9 ? what is "U" ?

NO	DATE	TIME-IN	LUNCH-IN	LUNCH-OUT	TIME-OUT	T.W.H	mean TWH
1	01-Oct-17	6:55 AM	12:30 PM	1:30 PM	4:00 PM	08:05	08:05
2	02-Oct-17	6:45 AM	12:30 PM	1:30 PM	4:00 PM	08:15	08:10	####
3	03-Oct-17	6:45 AM	12:30 PM	1:30 PM	4:00 PM	08:15	08:11
4	04-Oct-17	7:00 AM	12:30 PM	1:30 PM	4:00 PM	08:00	08:08
5	05-Oct-17	6:52 AM	12:30 PM	1:30 PM	3:50 PM	07:58	08:06
		####
		=SUM($G$2:G3)/(ROW()-1)

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

 

[Laxieryuah]

im sorry i did'nt inform clearly I9=mean the location of the data in excel which is the t.w.h mean of total working hour.
U= is representing for "undertime" which is i want to display when the I9 is below 8 hrs.now the main thing is;
1) first i want to know is. i want to get and transfer the value on the other table.
sample: my data is on I9 w/ value of 8:15
i want to get the data and to transfer in f15.
with this condition, if I9=23:00 leave it blank, if I9>7 i want to write 8 and if not make "u" display mean of undertime

2) once i completed to transfer the data i want to make total of it. and the total hrs make display in F25 assuming.

I hope everyone can understand my though.

 

[oldbrewer]

easy to add u if daily total less than 8, and to quantify the undertime. and to display mean undertime. But I am not at all clear what you want.

 

[Laxieryuah]

Ok to make it easy. could you help me to total the working hour in a month
without the day of absent.
again let say I9=8:15 mean he completed the work per day
I10=7:00 mean he's undertime
I11=23:00 mean he's absent or no data or off
i want to add this total hour of these value w/o the value of I11. since no data on that particular cell.
now i want to make a result of total man hour like this "15 Hrs"
meaning adding of I9 & I10 even though the value of I9 is 8:15.
i hope this is clear enough. im sorry i cant xplain properly.

 

[oldbrewer]

NO

DATE

TIME-IN

LUNCH-IN

LUNCH-OUT

TIME-OUT

T.W.H

mean TWH

running total decimal hours

hours so far this month

32.300

1

01-Oct-17

6:55 AM

12:30 PM

1:30 PM

4:00 PM

08:05

08:05

0.337

as hh:mm

32:17

2

02-Oct-17

6:45 AM

12:30 PM

1:30 PM

4:00 PM

08:15

08:10

0.681

3

03-Oct-17

00:00

08:10

0.681

4

04-Oct-17

7:00 AM

12:30 PM

1:30 PM

4:00 PM

08:00

08:06

1.014

5

05-Oct-17

6:52 AM

12:30 PM

1:30 PM

3:50 PM

07:58

08:04

1.346

person did not come in on October 3

so TWH for that day is zero

the mean TWH recognises time of zero

and divides running total by 2 not 3

so on 4 working days he worked 1.346 days

cell L1 converts the last number in column I (1.346) to decimal hours

and L2 as hours and minutes

if this data were for say 10 men

ie times for each man each day

fairly easy to do a summary for each man

tell us what else you need

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

 

[Laxieryuah]

Many thanks man.
so how bout this. getting total with exact value.
for sample in oct. 1 he's total hour is 8:15 i want to round off in 8 hrs
Oct.2 he's total hour is 7:50 i want to round off in 8 hrs. since this time sheet only for the contractor mean we paid only for 8 hrs.
Oct.3 he's absent so therefore it will show 23:00 based in my formula of computing total hrs less the break hrs a day.
now to make a summary i want to add total man hrs from oct 1 to oct 30 w/o adding the value of 23:00 during absent or day-off.

thank you very much for helping me a lot.thanks to the group also.

 

[Laxieryuah]

EMP.NAME

1-Oct-17

2-Nov-17

3-Nov-17

4-Nov-17

5-Nov-17

6-Nov-17

7-Nov-17

8-Nov-17

9-Nov-17

10-Nov-17

11-Nov-17

12-Nov-17

13-Nov-17

14-Nov-17

15-Nov-17

16-Nov-17

17-Nov-17

18-Nov-17

19-Nov-17

20-Nov-17

21-Nov-17

22-Nov-17

23-Nov-17

24-Nov-17

25-Nov-17

26-Nov-17

27-Nov-17

28-Nov-17

29-Nov-17

30-Nov-17

twh/month

BILAL

8:05

8:15

8:15

8:00

7:58

23:00

8:13

23:00

8:05

8:12

23:00

8:10

23:00

8:10

8:10

8:06

8:00

8:05

23:00

23:00

7:57

8:10

8:15

8:12

8:00

8:09

23:00

8:15

8:10

8:10

8:11

348:02

<tbody>
</tbody>

this is what exactly the value i have.as you can see even the value of absent or day-off still they're getting. i want to automatically avoid to add those number and make whole value what like i said.
i hope this exact value could help me to explain.

 

[oldbrewer]

8:15 = 8:00
07:50 = 8:00
is 08:16 = 08:30
what is 08:35

easy to give 23 hours foe absent day, but why ? If it is zero it will not affect the total ?

 

[Laxieryuah]

yes i want that to be fix like that
8:15 = 8:00
7:50 = 8:00
but please see my formula what ive use to get actual hrs per day of the person.
=SUM(IF(E13 <d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)< font=""><d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)< font=""><d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)< font=""><D13,E13+1,E13)-D13,IF(H13<F13,H13+1,H13)-F13)
</d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)<><d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)< font="">so when the person is absent or dayoff it will show 23:00 which actual value is zero.
so im looking the right formula to use. that will get the final total in a month with the exact value
what you mention.
Oct 1 8:15 = 8:00
Oct 2 7:50 = 8:00
oct 3 absent= 23:00
total it should be= 16 hrs in this given.
but when I'm trying to sum it up they add also the value of 23:00.
sorry for the inconvenient I'm new only in the excel world.
and i cannot attached the photo in here.</d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)<><d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)< font=""></d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)<></d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)<></d13,e13+1,e13)-d13,if(h13<f13,h13+1,h13)-f13)<>