if condition with weekdays

U

uswyne

Board Regular
Joined
Jul 27, 2017
Messages
78
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
[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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
=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"



etaf said:
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
Click to expand...
 
Upvote 0
Code: 
=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","??")))))))
 
Upvote 0
Thanks you so much. Can you please tell me whats wrong with my formula?

etaf said:
Sheet1

ABCDEF
Date2WeekdayTime InTime 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
CellFormula
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
Click to expand...
 
Upvote 0
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>
etaf said:
if you post all the formula
Click to expand...
 
Upvote 0
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>
 
Last edited:
Upvote 0
Thak you so much

etaf said:
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>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
359
AYouQueTai
A
C
  • Solved
Countifs Number Of Times Team Play on each weekday
Replies
3
Views
312
CordingBags
C
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
258
Joe4
Joe4
J
  • Solved
conditional formatting with multiple criteria
Replies
8
Views
392
jbrown021286
J
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
787
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top