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.

 

[oldbrewer]

what is I9 ? what is "U" ?

[TABLE="width: 661"]
<colgroup><col span="4"><col><col><col span="4"></colgroup><tbody>[TR]
[TD]NO[/TD]
[TD]DATE[/TD]
[TD]TIME-IN[/TD]
[TD]LUNCH-IN[/TD]
[TD]LUNCH-OUT[/TD]
[TD]TIME-OUT[/TD]
[TD]T.W.H[/TD]
[TD="colspan: 2"]mean TWH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-Oct-17[/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:05[/TD]
[TD="align: right"]08:05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]02-Oct-17[/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:15[/TD]
[TD="align: right"]08:10[/TD]
[TD]####[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]03-Oct-17[/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:15[/TD]
[TD="align: right"]08:11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]04-Oct-17[/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="align: right"]08:08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]05-Oct-17[/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"]07:58[/TD]
[TD="align: right"]08:06[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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="colspan: 3"]=SUM($G$2:G3)/(ROW()-1)[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 1026"]
<colgroup><col span="7"><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]NO[/TD]
[TD]DATE[/TD]
[TD]TIME-IN[/TD]
[TD]LUNCH-IN[/TD]
[TD]LUNCH-OUT[/TD]
[TD]TIME-OUT[/TD]
[TD]T.W.H[/TD]
[TD]mean TWH[/TD]
[TD]running total decimal hours[/TD]
[TD][/TD]
[TD]hours so far this month[/TD]
[TD="align: right"]32.300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-Oct-17[/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:05[/TD]
[TD="align: right"]08:05[/TD]
[TD="align: right"]0.337[/TD]
[TD][/TD]
[TD]as hh:mm[/TD]
[TD]32:17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]02-Oct-17[/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:15[/TD]
[TD="align: right"]08:10[/TD]
[TD="align: right"]0.681[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]03-Oct-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]08:10[/TD]
[TD="align: right"]0.681[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]04-Oct-17[/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="align: right"]08:06[/TD]
[TD="align: right"]1.014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]05-Oct-17[/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"]07:58[/TD]
[TD="align: right"]08:04[/TD]
[TD="align: right"]1.346[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]person did not come in on October 3[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]so TWH for that day is zero[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the mean TWH recognises time of zero[/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: 4"]and divides running total by 2 not 3[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]so on 4 working days he worked 1.346 days[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]cell L1 converts the last number in column I (1.346) to decimal hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and L2 as hours and minutes[/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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]if this data were for say 10 men[/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: 3"]ie times for each man each day[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]fairly easy to do a summary for each man[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]tell us what else you need[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 1238"]
<tbody>[TR]
[TD]EMP.NAME[/TD]
[TD]1-Oct-17[/TD]
[TD]2-Nov-17[/TD]
[TD]3-Nov-17[/TD]
[TD]4-Nov-17[/TD]
[TD]5-Nov-17[/TD]
[TD]6-Nov-17[/TD]
[TD]7-Nov-17[/TD]
[TD]8-Nov-17[/TD]
[TD]9-Nov-17[/TD]
[TD]10-Nov-17[/TD]
[TD]11-Nov-17[/TD]
[TD]12-Nov-17[/TD]
[TD]13-Nov-17[/TD]
[TD]14-Nov-17[/TD]
[TD]15-Nov-17[/TD]
[TD]16-Nov-17[/TD]
[TD]17-Nov-17[/TD]
[TD]18-Nov-17[/TD]
[TD]19-Nov-17[/TD]
[TD]20-Nov-17[/TD]
[TD]21-Nov-17[/TD]
[TD]22-Nov-17[/TD]
[TD]23-Nov-17[/TD]
[TD]24-Nov-17[/TD]
[TD]25-Nov-17[/TD]
[TD]26-Nov-17[/TD]
[TD]27-Nov-17[/TD]
[TD]28-Nov-17[/TD]
[TD]29-Nov-17[/TD]
[TD]30-Nov-17[/TD]
[TD][/TD]
[TD]twh/month[/TD]
[/TR]
[TR]
[TD]BILAL[/TD]
[TD]8:05[/TD]
[TD]8:15[/TD]
[TD]8:15[/TD]
[TD]8:00[/TD]
[TD]7:58[/TD]
[TD]23:00[/TD]
[TD]8:13[/TD]
[TD]23:00[/TD]
[TD]8:05[/TD]
[TD]8:12[/TD]
[TD]23:00[/TD]
[TD]8:10[/TD]
[TD]23:00[/TD]
[TD]8:10[/TD]
[TD]8:10[/TD]
[TD]8:06[/TD]
[TD]8:00[/TD]
[TD]8:05[/TD]
[TD]23:00[/TD]
[TD]23:00[/TD]
[TD]7:57[/TD]
[TD]8:10[/TD]
[TD]8:15[/TD]
[TD]8:12[/TD]
[TD]8:00[/TD]
[TD]8:09[/TD]
[TD]23:00[/TD]
[TD]8:15[/TD]
[TD]8:10[/TD]
[TD]8:10[/TD]
[TD]8:11[/TD]
[TD]348:02[/TD]
[/TR]
</tbody>[/TABLE]
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)<>