Simple Formula is Not working for Hours Check.

[Zack92]

Hello,

I'm trying to sort out a problem with my formula, but it's not working as expected. I want to filter out any cases that were created after 6 PM MST. The thing is, my data is in UTC, so I need to convert it to MST. If I do the conversion, it should exclude anything created after Friday 12:00 AM UTC. I set it up that way, but it's still showing some cases that were created before Friday 6:00 PM MST. Can anyone help me figure out what's wrong with my formula?

OffHours =
IF (
    // Check if the day is Friday and time is after 6 PM Mountain
    (
        WEEKDAY ( df_Case[CreatedDate] ) = 6 // Friday
            && HOUR ( df_Case[CreatedDateTime (UTC)] ) >= 0 // 6 PM Mountain
    )
        || // Check if the day is Saturday and time is before 8 PM Mountain
        (
            WEEKDAY ( df_Case[CreatedDate] ) = 7 // Saturday
                && HOUR ( df_Case[CreatedDateTime (UTC)] ) < 3 // 8 PM Mountain
        )
        || // Check if the day is Sunday and time is after 7 AM Mountain
        (
            WEEKDAY ( df_Case[CreatedDate] ) = 1 // Sunday
                && HOUR ( df_Case[CreatedDateTime (UTC)] ) >= 14 // 7 AM Mountain
        )
        || // Check if the day is Sunday and time is before 5 PM Mountain
        (
            WEEKDAY ( df_Case[CreatedDate] ) = 1 // Sunday
                && HOUR ( df_Case[CreatedDateTime (UTC)] ) < 17 // 5 PM Mountain
        ),
    "OffHours",
    "Other"
)

File - 

https://drive.google.com/file/d/1Bh1GJG74fXSyUsWn2iH1uqQB3khsltAG/view?usp=sharing

Thank you so much 

[samratpbi · Accepted solution]

Hi, I have added a table with case and date time in UTC time zone and converted it to local time zone, for you it would be MST.
let
Source = Excel.Workbook(File.Contents("C:\xxxxxx\xx\xxxx\datetime.xlsx"), null, true),
Datetime_Sheet = Source{[Item="Datetime",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Datetime_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Case", Int64.Type}, {"CreateDateTime(UTC)", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"CreateDateTime(UTC)", type datetimezone}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"CreateDateTime(UTC)", type datetime}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "CreateDateTime(UTC)", "CreateDateTime(UTC) - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"CreateDateTime(UTC) - Copy", type text}}, "en-IN"), "CreateDateTime(UTC) - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CreateDateTime(UTC) - Copy.1", "CreateDateTime(UTC) - Copy.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"CreateDateTime(UTC) - Copy.1", type date}, {"CreateDateTime(UTC) - Copy.2", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "WeekDay", each Date.DayOfWeek([#"CreateDateTime(UTC) - Copy.1"], Day.Sunday)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"WeekDay", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"CreateDateTime(UTC) - Copy.1", "LocalDate"}, {"CreateDateTime(UTC) - Copy.2", "LocalTime"}})
in
#"Renamed Columns"

After that table would look like below:

Then created a conditional column ( I considered anything on Saturday, Sunday and before 9 am or after 6 pm would be off work hours)

OffHrsCheck =

IF(Datetime[WeekDay] IN {0,6},

    "OffHrs",

    IF(VALUE(LEFT(Datetime[LocalTime],2)) <9 || VALUE(LEFT(Datetime[LocalTime],2)) >= 18, "OffHrs", "WorkHrs")

)

If this resolves your problem then please make it as solution.

[lbendlin]

Did you mean MST or MDT?  MST is UTC-7, so did you mean Saturday 1am UTC?

To convert UTC datetime to MST, subtract 7/24  .  To convert UTC to MDT, subtract 0.25

Your calculated column seems to have many more rules.  Please elaborate.

Note: There is absolutely nothing simple about timezone math.