I have a scenario here to calculate working hours
I have given a format in the way as belowI have attached the excel as well)
9-Feb-13 8-Feb-13 7-Feb-13 6-Feb-13 5-Feb-13 4-Feb-13 Days Worked Total Working Hrs
5:56 10:21 10:09 9:43 10:27 10:41 6.0 57:17
7:12 9:56 9:32 10:04 9:30 9:59 6.0 56:13
7:23 9:27 9:19 9:40 9:06 9:29 6.0 54:24
7:30 9:02 9:29 9:10 9:16 9:16 6.0 53:43
Condtition1:
If given date falls on weekday - 9 hours working
If given day falls on Saturday - 7 hours working
Condition2:
Working Hours > 70% - Full Day(Return 1)
Working Hours > 40% and < 70%- Half Day(Return 0.5)
Working Hours < 40% - Absent(Return 0)
Scenario
If working Hours of an employee is above 70%(out of 9 hrs) on weekday, then we can count it as 1 day
If working Hours of an employee is in between 40% to 70% on weekday, then we can count it as 0.5 day
If working Hours of an employee is less than 40% on weekday, then it can be considered as leave
If working Hours of an employee is above 70%(out of 7 hrs) on Saturday, then we can count it as 1 day
If working Hours of an employee is in between 40% to 70% on Saturday, then we can count it as 0.5 day
If working Hours of an employee is less than 40% on Saturday, then it can be considered as leave
I have difficulty in calculating column "R" in attached excel(Green color highlighted)
If I enter formula
=VALUE(IF(OR(MOD(WEEKDAY(B$1),7)=0,MOD(WEEKDAY(B$1),7)=1)=TRUE,IF((B4*24)/7*100>70,"7",IF(AND((B4*24)/7*100>40,(B4*24)/7*100<70),"3.5","0")),IF((B4*24)/9*100>70,"9",IF(AND((B4*24)/9*100>40,(B4*24)/7*100<90),"4.5","0"))))+VALUE(IF(OR(MOD(WEEKDAY(C$1),7)=0,MOD(WEEKDAY(C$1),7)=1)=TRUE,IF((C4*24)/7*100>70,"7",IF(AND((C4*24)/7*100>40,(C4*24)/7*100<70),"3.5","0")),IF((C4*24)/9*100>70,"9",IF(AND((C4*24)/9*100>40,(C4*24)/7*100<90),"4.5","0"))))+VALUE(IF(OR(MOD(WEEKDAY(D$1),7)=0,MOD(WEEKDAY(D$1),7)=1)=TRUE,IF((D4*24)/7*100>70,"7",IF(AND((D4*24)/7*100>40,(D4*24)/7*100<70),"3.5","0")),IF((D4*24)/9*100>70,"9",IF(AND((D4*24)/9*100>40,(D4*24)/7*100<90),"4.5","0"))))+VALUE(IF(OR(MOD(WEEKDAY(E$1),7)=0,MOD(WEEKDAY(E$1),7)=1)=TRUE,IF((E4*24)/7*100>70,"7",IF(AND((E4*24)/7*100>40,(E4*24)/7*100<70),"3.5","0")),IF((E4*24)/9*100>70,"9",IF(AND((E4*24)/9*100>40,(E4*24)/7*100<90),"4.5","0"))))+VALUE(IF(OR(MOD(WEEKDAY(F$1),7)=0,MOD(WEEKDAY(F$1),7)=1)=TRUE,IF((F4*24)/7*100>70,"7",IF(AND((F4*24)/7*100>40,(F4*24)/7*100<70),"3.5","0")),IF((F4*24)/9*100>70,"9",IF(AND((F4*24)/9*100>40,(F4*24)/7*100<90),"4.5","0"))))+VALUE(IF(OR(MOD(WEEKDAY(G$1),7)=0,MOD(WEEKDAY(G$1),7)=1)=TRUE,IF((G4*24)/7*100>70,"1",IF(AND((G4*24)/7*100>40,(G4*24)/7*100<70),"0.5","0")),IF((G4*24)/9*100>70,"1",IF(AND((G4*24)/9*100>40,(G4*24)/7*100<90),"0.5","0"))))
An error occurs telling "The specified formula cannot be entered because it contains more values, references and/or names than are allowed in the current format"
Please help
Regards
Shiva
Bookmarks