Forum Discussion
IF Statement - Times
HI,
Trying to create categories of the shift types so we can track the different types of shifts. I using the New Column tool in the Data view.
This is what I've got so far (I'm going to do Friday, Saturday and Sunday once I've done te first section)
Shifts =
IF(Sheet1[Weekday] = "Monday" || Sheet1[Weekday] ="Tuesday" || Sheet1[Weekday] = "Wednesday" || Sheet1[Weekday] ="Thursday" && Sheet1[Time] < TIME(8,45,00), "Day Shift",
IF(Sheet1[Weekday]= "Monday" ||Sheet1[Weekday] ="Tuesday" || Sheet1[Weekday] = "Wednesday" || Sheet1[Weekday] ="Thursday" && Sheet1[Time] < TIME(17,30,00), "Late Shift",
IF(Sheet1[Weekday] = "Monday" ||Sheet1[Weekday]="Tuesday" || Sheet1[Weekday] = "Wednesday" || Sheet1[Weekday] ="Thursday" && Sheet1[Time]< TIME(19,30,00), "Late Late",
IF(Sheet1[Weekday] = "Monday" || Sheet1[Weekday]="Tuesday" || Sheet1[Weekday] = "Wednesday" || Sheet1[Weekday]="Thursday" && Sheet1[Time] > TIME(08,45,00), "Out of Office",
IF(Sheet1[Weekday] = "Monday" ||Sheet1[Weekday] ="Tuesday" || Sheet1[Weekday] = "Wednesday" ||Sheet1[Weekday] ="Thursday" && Sheet1[Time] <= TIME(23,59,00), "Out of Office")))))
The shift patterns for Monday - Thursday are the following...
00:00:00 -> 08:44:59 = Out of Office
08:45:00 -> 17:29:59 = Day Shift
17:30:00 -> 19:29:59 = Late Shift
19:30:00 ->21:29:59 = Late Late Shift
21:30:00 -> 23:59:59 = Out of Office
My problem is that there seems to be a bit of overlap with the IF statements
Would the best solution be to have an IF Statement for each shift on each day, like the code below
IF(Sheet1[Weekday] = "Monday" && Sheet1[Time] < TIME(8,45,00), "Day Shift")
I'll add the PBIX file below.
https://www.dropbox.com/s/4pbc79tpo9yg59a/help.pbix?dl=0
michael_knight are you getting syntax error, you need to close the switch bracket ) before 2nd if
Shifts = var MondThurs = {"Monday", "Tuesday", "Wednesday", "Thursday"} var friday ={"Friday"} var Saturday = {"Saturday"} var Sunday = {"Sunday"} return IF( 'Lead'[Weekday] IN MondThurs, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN friday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,00,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN Saturday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN Sunday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ) ))))I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
5 Replies
michael_knight try this, it is much cleaner look and you can tweak as per your need
Shifts = VAR _Days = { "Monday", "Tuesday", "Wednesday", "Thursday" } RETURN IF ( Sheet1[WeekDay] IN _Days, SWITCH ( TRUE(), Sheet1[Time] >= TIME(21,30,00), "Out of Office", Sheet1[Time] >= TIME(19,30,00), "Late Late Shift", Sheet1[Time] >= TIME(17,30,00), "Late Shift", Sheet1[Time] >= TIME(08,45,00), "Day Shift", Sheet1[Time] > TIME(00,00,00), "Out of Office" ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
That's amazing, thank you parry2k
Just a quick one, I tried to incorperate Friday, Saturday and Sunday into this. I can't seem to get them working, are you able to notice anything I'm doing wrong?
Shifts = var MondThurs = {"Monday", "Tuesday", "Wednesday", "Thursday"} var friday ={"Friday"} var Saturday = {"Saturday"} var Sunday = {"Sunday"} return IF( 'Lead'[Weekday] IN MondThurs, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office", IF( 'Lead'[Weekday] IN friday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,00,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office", IF( 'Lead'[Weekday] IN Saturday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office", IF( 'Lead'[Weekday] IN Sunday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office"))))))))Cheers,
Mike
michael_knight are you getting syntax error, you need to close the switch bracket ) before 2nd if
Shifts = var MondThurs = {"Monday", "Tuesday", "Wednesday", "Thursday"} var friday ={"Friday"} var Saturday = {"Saturday"} var Sunday = {"Sunday"} return IF( 'Lead'[Weekday] IN MondThurs, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN friday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(19,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(17,00,00), "Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN Saturday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ), IF( 'Lead'[Weekday] IN Sunday, SWITCH( TRUE(), 'Lead'[createdon - Copy.1] >= TIME(21,30,00), "Out of Office", 'Lead'[createdon - Copy.1] >= TIME(17,30,00), "Late Late Shift", 'Lead'[createdon - Copy.1] >= TIME(08,45,00), "Day Shift", 'Lead'[createdon - Copy.1] > TIME(00,00,00), "Out of Office" ) ))))I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
