if condition with weekdays

[uswyne]

[TABLE="class: grid, width: 405"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]Formula use[/TD]
[/TR]
[TR]
[TD]Date2[/TD]
[TD]Weekday[/TD]
[TD]Time In [/TD]
[TD]Time Out[/TD]
[TD]Status[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21-Oct-17[/TD]
[TD]Saturday[/TD]
[TD]9:00:00[/TD]
[TD]14:00:00[/TD]
[TD]Present[/TD]
[TD]=IF(D3="","",IF(D3="Sunday","Off Day",IF(E3="","Absent",IF(F3="","Absent",IF(D3="Saturday",(IF(F3<TIME(14,0,0),"Early Going",IF(D3="Saturday",(IF(E3>TIME(9,0,59),"Late Coming","Present")),(IF(E3>=TIME(7,30,59),"Late Coming",IF(F3<TIME(15,30,0),"Early Going","Present")))))))))))[/TD]
[/TR]
[TR]
[TD]22-Oct-17[/TD]
[TD]Saturday[/TD]
[TD]9:00:00[/TD]
[TD]14:00:00[/TD]
[TD]Present[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24-Oct-17[/TD]
[TD]Monday[/TD]
[TD]7:30:00[/TD]
[TD]15:30:00[/TD]
[TD]FALSE[/TD]
[TD]how to meet these below conditions[/TD]
[/TR]
[TR]
[TD]25-Oct-17[/TD]
[TD]Tuesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26-Oct-17[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27-Oct-17[/TD]
[TD]Thursday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28-Oct-17[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Absent[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

formula I have entered is not working for weekdays, kindly help me on this regard.

Thanks

 

[etaf]

cant see all the formula , but its not constructed correctly as you have an IF with a Test, TRUE and FALSE condition
(IF(F3<time(14,0,0),"early going",if(d3="Saturday" ,(if(e3="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">TIME(9,0,59),"Late Coming","Present")),
</time(14,0,0),"early>which will stop the formula working


But the rules you are setting up

1) weekday is blank then leave cell blank
2) weekday is Sunday , cell = "day off"
3) Stop time is blank , cell = "absent"
4) weekday is Saturday - then start time should be 9:00am if start time is before 09:00:59 then "Present" otherwise "late"
5) weekday is Monday to Friday - then then start time should be 7:30am if start time is before 07:30:59 then "Present" otherwise "late"
6) any rules for time out ?
7) any other rules ?

Once we have ALL the rules we can help put a solution together

 

[uswyne]

=IF(F3="","",IF(F3="Sunday","Off Day",IF(G3="","Absent",IF(H3="","Absent",IF(F3="Saturday",(IF(G3>TIME(9,0,59),"Late Coming","Present")),IF(F3="Saturday",(IF(H3<TIME(14,0,0),"Early Going","Present")),IF(H3<TIME(15,30,0),"Early Going",(IF(G3>TIME(7,30,59),"Late Coming","Present")))))))))

6) if weekday is Saturday - then end time should be =>14:00:59pm if end time is before 14:00:59 then "Early Going" otherwise "Present"
7) weekday is Monday to Friday - then end time should be =>15:30:59pm if end time is before 15:30:59 then "Early Going" otherwise "Present"

cant see all the formula , but its not constructed correctly as you have an IF with a Test, TRUE and FALSE condition
(IF(F3<time(14,0,0),"early going",if(d3="Saturday" ,(if(e3="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">TIME(9,0,59),"Late Coming","Present")),
</time(14,0,0),"early>which will stop the formula working


But the rules you are setting up

1) weekday is blank then leave cell blank
2) weekday is Sunday , cell = "day off"
3) Stop time is blank , cell = "absent"
4) weekday is Saturday - then start time should be 9:00am if start time is before 09:00:59 then "Present" otherwise "late"
5) weekday is Monday to Friday - then then start time should be 7:30am if start time is before 07:30:59 then "Present" otherwise "late"
6) any rules for time out ?
7) any other rules ?

Once we have ALL the rules we can help put a solution together

 

[etaf]

Excel Workbook
	A	B	C	D	E	F
1	Date2	Weekday	Time In	Time Out		Status
2	21-Oct-17	Saturday	09:00:00	14:00:00		present
3	22-Oct-17	Saturday	09:00:00	14:00:00		present
4	24-Oct-17	Monday	07:30:00	15:30:00		present
5	25-Oct-17	Tuesday	08:00	13:00		Late Start & Early Going
6	26-Oct-17	Wednesday				Absent
7	27-Oct-17	Sunday				DayOff
8	28-Oct-17	monday	09:30	17:00		Late Start
Sheet1

 

[etaf]

=IF(B2="","",IF(B2="sunday","DayOff",IF(D2="","Absent",IF(OR(AND(B2="Saturday",C2<=TIME(9,0,59),D2>=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2<=TIME(7,30,59),D2>=TIME(15,30,0))),"present",IF(OR(AND(B2="Saturday",C2>=TIME(9,1,0),D2>=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2>=TIME(7,31,0),D2>=TIME(15,30,0))),"Late Start",  IF(OR(AND(B2="Saturday",C2<=TIME(9,0,59),D2<=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2<=TIME(7,30,59),D2<=TIME(15,30,0))), "Early Going",IF(OR(AND(B2="Saturday",C2>=TIME(9,0,59),D2<=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2>=TIME(7,30,59),D2<=TIME(15,30,0))), "Late Start & Early Going","??")))))))

 

[uswyne]

Thanks you so much. Can you please tell me whats wrong with my formula?

Sheet1

	A	B	C	D	E	F
Date2	Weekday	Time In	Time Out		Status
Saturday		present
Saturday		present
Monday		present
Tuesday		Late Start & Early Going
Wednesday				Absent
Sunday				DayOff
	Late Start

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:108px;"><col style="width:119px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:152px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]21-Oct-17[/TD]

[TD="align: right"]09:00:00[/TD]
[TD="align: right"]14:00:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]22-Oct-17[/TD]

[TD="align: right"]09:00:00[/TD]
[TD="align: right"]14:00:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]24-Oct-17[/TD]

[TD="align: right"]07:30:00[/TD]
[TD="align: right"]15:30:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]25-Oct-17[/TD]

[TD="align: right"]08:00[/TD]
[TD="align: right"]13:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]26-Oct-17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]27-Oct-17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]28-Oct-17[/TD]
[TD="align: left"]monday[/TD]
[TD="align: left"]09:30[/TD]
[TD="align: left"]17:00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF(B2="","",IF(B2="sunday","DayOff",IF(D2="","Absent",IF(OR(AND(B2="Saturday",C2<=TIME(9,0,59),D2>=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2<=TIME(7,30,59),D2>=TIME(15,30,0))),"present",IF(OR(AND(B2="Saturday",C2>=TIME(9,1,0),D2>=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2>=TIME(7,31,0),D2>=TIME(15,30,0))),"Late Start", IF(OR(AND(B2="Saturday",C2<=TIME(9,0,59),D2<=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2<=TIME(7,30,59),D2<=TIME(15,30,0))), "Early Going",IF(OR(AND(B2="Saturday",C2>=TIME(9,0,59),D2<=TIME(14,0,0)),AND(B2<>"Saturday",B2<>"Sunday",C2>=TIME(7,30,59),D2<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F3 =IF(B3="","",IF(B3="sunday","DayOff",IF(D3="","Absent",IF(OR(AND(B3="Saturday",C3<=TIME(9,0,59),D3>=TIME(14,0,0)),AND(B3<>"Saturday",B3<>"Sunday",C3<=TIME(7,30,59),D3>=TIME(15,30,0))),"present",IF(OR(AND(B3="Saturday",C3>=TIME(9,1,0),D3>=TIME(14,0,0)),AND(B3<>"Saturday",B3<>"Sunday",C3>=TIME(7,31,0),D3>=TIME(15,30,0))),"Late Start", IF(OR(AND(B3="Saturday",C3<=TIME(9,0,59),D3<=TIME(14,0,0)),AND(B3<>"Saturday",B3<>"Sunday",C3<=TIME(7,30,59),D3<=TIME(15,30,0))), "Early Going",IF(OR(AND(B3="Saturday",C3>=TIME(9,0,59),D3<=TIME(14,0,0)),AND(B3<>"Saturday",B3<>"Sunday",C3>=TIME(7,30,59),D3<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F4 =IF(B4="","",IF(B4="sunday","DayOff",IF(D4="","Absent",IF(OR(AND(B4="Saturday",C4<=TIME(9,0,59),D4>=TIME(14,0,0)),AND(B4<>"Saturday",B4<>"Sunday",C4<=TIME(7,30,59),D4>=TIME(15,30,0))),"present",IF(OR(AND(B4="Saturday",C4>=TIME(9,1,0),D4>=TIME(14,0,0)),AND(B4<>"Saturday",B4<>"Sunday",C4>=TIME(7,31,0),D4>=TIME(15,30,0))),"Late Start", IF(OR(AND(B4="Saturday",C4<=TIME(9,0,59),D4<=TIME(14,0,0)),AND(B4<>"Saturday",B4<>"Sunday",C4<=TIME(7,30,59),D4<=TIME(15,30,0))), "Early Going",IF(OR(AND(B4="Saturday",C4>=TIME(9,0,59),D4<=TIME(14,0,0)),AND(B4<>"Saturday",B4<>"Sunday",C4>=TIME(7,30,59),D4<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F5 =IF(B5="","",IF(B5="sunday","DayOff",IF(D5="","Absent",IF(OR(AND(B5="Saturday",C5<=TIME(9,0,59),D5>=TIME(14,0,0)),AND(B5<>"Saturday",B5<>"Sunday",C5<=TIME(7,30,59),D5>=TIME(15,30,0))),"present",IF(OR(AND(B5="Saturday",C5>=TIME(9,1,0),D5>=TIME(14,0,0)),AND(B5<>"Saturday",B5<>"Sunday",C5>=TIME(7,31,0),D5>=TIME(15,30,0))),"Late Start", IF(OR(AND(B5="Saturday",C5<=TIME(9,0,59),D5<=TIME(14,0,0)),AND(B5<>"Saturday",B5<>"Sunday",C5<=TIME(7,30,59),D5<=TIME(15,30,0))), "Early Going",IF(OR(AND(B5="Saturday",C5>=TIME(9,0,59),D5<=TIME(14,0,0)),AND(B5<>"Saturday",B5<>"Sunday",C5>=TIME(7,30,59),D5<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F6 =IF(B6="","",IF(B6="sunday","DayOff",IF(D6="","Absent",IF(OR(AND(B6="Saturday",C6<=TIME(9,0,59),D6>=TIME(14,0,0)),AND(B6<>"Saturday",B6<>"Sunday",C6<=TIME(7,30,59),D6>=TIME(15,30,0))),"present",IF(OR(AND(B6="Saturday",C6>=TIME(9,1,0),D6>=TIME(14,0,0)),AND(B6<>"Saturday",B6<>"Sunday",C6>=TIME(7,31,0),D6>=TIME(15,30,0))),"Late Start", IF(OR(AND(B6="Saturday",C6<=TIME(9,0,59),D6<=TIME(14,0,0)),AND(B6<>"Saturday",B6<>"Sunday",C6<=TIME(7,30,59),D6<=TIME(15,30,0))), "Early Going",IF(OR(AND(B6="Saturday",C6>=TIME(9,0,59),D6<=TIME(14,0,0)),AND(B6<>"Saturday",B6<>"Sunday",C6>=TIME(7,30,59),D6<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F7 =IF(B7="","",IF(B7="sunday","DayOff",IF(D7="","Absent",IF(OR(AND(B7="Saturday",C7<=TIME(9,0,59),D7>=TIME(14,0,0)),AND(B7<>"Saturday",B7<>"Sunday",C7<=TIME(7,30,59),D7>=TIME(15,30,0))),"present",IF(OR(AND(B7="Saturday",C7>=TIME(9,1,0),D7>=TIME(14,0,0)),AND(B7<>"Saturday",B7<>"Sunday",C7>=TIME(7,31,0),D7>=TIME(15,30,0))),"Late Start", IF(OR(AND(B7="Saturday",C7<=TIME(9,0,59),D7<=TIME(14,0,0)),AND(B7<>"Saturday",B7<>"Sunday",C7<=TIME(7,30,59),D7<=TIME(15,30,0))), "Early Going",IF(OR(AND(B7="Saturday",C7>=TIME(9,0,59),D7<=TIME(14,0,0)),AND(B7<>"Saturday",B7<>"Sunday",C7>=TIME(7,30,59),D7<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) F8 =IF(B8="","",IF(B8="sunday","DayOff",IF(D8="","Absent",IF(OR(AND(B8="Saturday",C8<=TIME(9,0,59),D8>=TIME(14,0,0)),AND(B8<>"Saturday",B8<>"Sunday",C8<=TIME(7,30,59),D8>=TIME(15,30,0))),"present",IF(OR(AND(B8="Saturday",C8>=TIME(9,1,0),D8>=TIME(14,0,0)),AND(B8<>"Saturday",B8<>"Sunday",C8>=TIME(7,31,0),D8>=TIME(15,30,0))),"Late Start", IF(OR(AND(B8="Saturday",C8<=TIME(9,0,59),D8<=TIME(14,0,0)),AND(B8<>"Saturday",B8<>"Sunday",C8<=TIME(7,30,59),D8<=TIME(15,30,0))), "Early Going",IF(OR(AND(B8="Saturday",C8>=TIME(9,0,59),D8<=TIME(14,0,0)),AND(B8<>"Saturday",B8<>"Sunday",C8>=TIME(7,30,59),D8<=TIME(15,30,0))), "Late Start & Early Going","??"))))))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[etaf]

Can you please tell me whats wrong with my formula?

if you post all the formula

 

[uswyne]

This I was trying
<time(14,0,0),"early going","present")),if(h3<time(15,30,0),"early="" going",(if(g3="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">
=IF(F3="","",IF(F3="Sunday","Off Day",IF(G3="","Absent",IF(H3="","Absent",IF(F3="Saturday",(IF(H3<TIME(14,0,0),"Early Going","Present")),IF(F3="Saturday",(IF(G3>TIME(9,0,59),"Late Coming","Present")),IF(H3<TIME(15,30,0),"Early Going",(IF(G3>TIME(7,30,59),"Late Coming","Present")))))))))

</time(14,0,0),"early>

if you post all the formula

 

[etaf]

the formula is
IF( TEST, TRUE, FALSE)

a nested if , as you have setup for the FALSE section

IF( TEST, TRUE, IF( TEST, TRUE, FALSE) )

so
=IF(F3="","",IF(F3="Sunday","Off Day",IF(G3="","Absent",IF(H3="","Absent",IF(F3="Saturday",(IF(H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="" style="color: rgb(51, 51, 51);">TIME(9,0,59),"Late Coming","Present")), - cant go any further as you have completed the formula in the last statement

you also have
H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="">TIME(9,0,59)
</time(14,0,0),"early>which will give an error - probably name error
as you have NO = > <

so the part
IF(H3<time(15,30,0),"early going",(if(g3="" style="background-color: rgb(250, 250, 250);">TIME(7,30,59),"Late Coming","Present")))))))))
</time(15,30,0),"early>will not be seen and also is complete formula

you also have exactly the same TRUE and FALSE parts in the last 2 IF sections
(IF(H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="">TIME(9,0,59),"Late Coming","Present")),IF(H3<time(15,30,0),"early going",(if(g3="">TIME(7,30,59),"Late Coming","Present")))))))))</time(15,30,0),"early></time(14,0,0),"early>

when you have two conditions with the same result
ie
IF(G3="","Absent",IF(H3="","Absent"

you can combine them with an OR
IF(OR ( G3="", H3="") ,"Absent",

In my formula for absent - you only wanted
3) Stop time is blank , cell = "absent"


</time(14,0,0),"early>

 

[uswyne]

Thak you so much

the formula is
IF( TEST, TRUE, FALSE)

a nested if , as you have setup for the FALSE section

IF( TEST, TRUE, IF( TEST, TRUE, FALSE) )

so
=IF(F3="","",IF(F3="Sunday","Off Day",IF(G3="","Absent",IF(H3="","Absent",IF(F3="Saturday",(IF(H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="" style="color: rgb(51, 51, 51);">TIME(9,0,59),"Late Coming","Present")), - cant go any further as you have completed the formula in the last statement

you also have
H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="">TIME(9,0,59)
</time(14,0,0),"early>which will give an error - probably name error
as you have NO = > <

so the part
IF(H3<time(15,30,0),"early going",(if(g3="" style="background-color: rgb(250, 250, 250);">TIME(7,30,59),"Late Coming","Present")))))))))
</time(15,30,0),"early>will not be seen and also is complete formula

you also have exactly the same TRUE and FALSE parts in the last 2 IF sections
(IF(H3<time(14,0,0),"early going","present")),if(f3="Saturday" ,(if(g3="">TIME(9,0,59),"Late Coming","Present")),IF(H3<time(15,30,0),"early going",(if(g3="">TIME(7,30,59),"Late Coming","Present")))))))))</time(15,30,0),"early></time(14,0,0),"early>

when you have two conditions with the same result
ie
IF(G3="","Absent",IF(H3="","Absent"

you can combine them with an OR
IF(OR ( G3="", H3="") ,"Absent",

In my formula for absent - you only wanted
3) Stop time is blank , cell = "absent"


</time(14,0,0),"early>