What will be the best way to add multiples date and time condition IF?

[Zack92]

Hello,

I trying to catch a bunch of cases open between Friday and Monday after office hours in different time zones. I came up with the following idea but it's not working. Can anybody please help me to fix the issue?

IF(
    (WEEKDAY(df_Case[CreatedDate]) >= 6 && WEEKDAY(df_Case[CreatedDate]) <= 2) && (HOUR(df_Case[CreatedDateTime]) >= 18 && HOUR(df_Case[CreatedDateTime]) <= 7),
    "Pager",
    IF(
        (WEEKDAY(df_Case[CreatedDate]) >= 5 && WEEKDAY(df_Case[CreatedDate]) <= 1) && (HOUR(df_Case[CreatedDateTime]) >= 7 && HOUR(df_Case[CreatedDateTime]) <= 20),
        "Pager",
        IF(
            (WEEKDAY(df_Case[CreatedDate]) >= 6 && WEEKDAY(df_Case[CreatedDate]) <= 2) && (HOUR(df_Case[CreatedDateTime]) >= 3 && HOUR(df_Case[CreatedDateTime]) <= 16),
            "Pager",
            "Other"
        )
    )

 Thank you so much 

[Zack92]

@amustafa, 

Thank you so much for the help. I can combine above formula like below right 

Test =
IF (
    df_Case[EndAccount.Region] = "NA",
    IF (
        // Check if the day is Friday and time is after 6 PM
        (
            WEEKDAY ( df_Case[CreatedDate] ) = 6
                && HOUR ( df_Case[CreatedDateTime] ) >= 18
        )
            || // Check if the day is Saturday or Sunday (whole day)
            (
                WEEKDAY ( df_Case[CreatedDate] ) = 1
                    || WEEKDAY ( df_Case[CreatedDate] ) = 7
            )
            || // Check if the day is Monday and time is before 8 AM
            (
                WEEKDAY ( df_Case[CreatedDate] ) = 2
                    && HOUR ( df_Case[CreatedDateTime] ) < 8
            ),
        "Pager",
        "Other"
    ),
    IF (
        df_Case[EndAccount.Region] = "IMETA",
        IF (
            // Check if the day is Thursday and time is after 7 PM
            (
                WEEKDAY ( df_Case[CreatedDate] ) = 5
                    && HOUR ( df_Case[CreatedDateTime] ) >= 19
            )
                || // Check if the day is Friday or Saturday (whole day)
                (
                    WEEKDAY ( df_Case[CreatedDate] ) = 6
                        || WEEKDAY ( df_Case[CreatedDate] ) = 7
                )
                || // Check if the day is Sunday and time is before 8 AM (20 in your time)
                (
                    WEEKDAY ( df_Case[CreatedDate] ) = 1
                        && HOUR ( df_Case[CreatedDateTime] ) < 20
                ),
            "Pager",
            "Other"
        ),
        ""
    )
)

[amustafa]

To adjust your formula for a different weekend schedule, such as Thursday to Saturday, as in some Middle Eastern countries, you need to modify the WEEKDAY() function values to align with the new weekend days. In your case, you want to adjust the logic for Thursday post 7 PM, Friday and Saturday the whole day, and Sunday before 8 AM.

Here's how the adjusted formula would look:

IF(
// Check if the day is Thursday and time is after 7 PM
(WEEKDAY(df_Case[CreatedDate]) = 5 && HOUR(df_Case[CreatedDateTime]) >= 19) ||
// Check if the day is Friday or Saturday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 6 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Sunday and time is before 8 AM (20 in your time)
(WEEKDAY(df_Case[CreatedDate]) = 1 && HOUR(df_Case[CreatedDateTime]) < 20),
"Pager",
"Other"
)

[amustafa · Accepted solution]

Let's assume business hours are from 8 AM to 6 PM. Therefore, we are looking at tickets opened after 6 PM on Friday until before 8 AM on Monday. The updated DAX code should look something like this:

IF(
// Check if the day is Friday and time is after 6 PM
(WEEKDAY(df_Case[CreatedDate]) = 6 && HOUR(df_Case[CreatedDateTime]) >= 18) ||
// Check if the day is Saturday or Sunday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 1 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Monday and time is before 8 AM
(WEEKDAY(df_Case[CreatedDate]) = 2 && HOUR(df_Case[CreatedDateTime]) < 8),
"Pager",
"Other"
)

[Zack92]

@amustafa 
Thank you so much for the quick response. This is great, one last question, If I have a different time like the Middle East, for example, Thursday to Sunday, I have to repost all the steps again right? Is there an efficient way 

Assume their Weekend start on Thursday (5) and Time after  (7 - this is covert to my time from their time)  Friday and Saturday, before Sunday (1) 7 AM (20 - convert to my time) 

IF(
// Check if the day is Friday and time is after 6 PM
(WEEKDAY(df_Case[CreatedDate]) = 6 && HOUR(df_Case[CreatedDateTime]) >= 18) ||
// Check if the day is Saturday or Sunday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 1 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Monday and time is before 8 AM
(WEEKDAY(df_Case[CreatedDate]) = 2 && HOUR(df_Case[CreatedDateTime]) < 8),
"Pager",
IF(
(WEEKDAY(df_Case[CreatedDate]) = 5 && HOUR(df_Case[CreatedDateTime]) >= 7) ||
(WEEKDAY(df_Case[CreatedDate]) = 6 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
(WEEKDAY(df_Case[CreatedDate]) = 1 && HOUR(df_Case[CreatedDateTime]) < 20),
"Pager",
"Other"
))

 Again, thank you so much