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
CDEFGFormula use
Date2WeekdayTime In Time OutStatus
21-Oct-17Saturday9:00:0014:00:00Present=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")))))))))))
22-Oct-17Saturday9:00:0014:00:00Present
24-Oct-17Monday7:30:0015:30:00FALSEhow to meet these below conditions
25-Oct-17Tuesday Absent
26-Oct-17Wednesday Absent
27-Oct-17Thursday Absent
28-Oct-17Friday Absent

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

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

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
1Date2WeekdayTime InTime Out Status
221-Oct-17Saturday09:00:0014:00:00 present
322-Oct-17Saturday09:00:0014:00:00 present
424-Oct-17Monday07:30:0015:30:00 present
525-Oct-17Tuesday08:0013:00 Late Start & Early Going
626-Oct-17Wednesday Absent
727-Oct-17Sunday DayOff
828-Oct-17monday09:3017:00 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>
</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
172
AYouQueTai
A
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
566
Peter_SSs
Peter_SSs
D
  • Question
VBA to Populate an Issue Status Cell
Replies
0
Views
290
Desiato34
D
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
419
MrDB4Excel
MrDB4Excel
M
  • Solved
Improving my formulas/approach for date/time analysis
Replies
9
Views
935
mediumrare
M

Forum statistics

Threads
1,215,461
Messages
6,124,952
Members
449,198
Latest member
MhammadishaqKhan

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