Working hours with UTC times

[rgalvez]

Hello experts!

I have two tables:
Table 1 has all the users list, which includes the time zone where each user lives.  Table 2 has a completion date for each user. Many completions per user, in UTC format.   Tables are linked one-to-many through the UserID.

Table 1 (one):

UserID	User Name	Time Zone
1	John Doe	EST
2	Jane Doe	CST
3	Jack Doe	PST

Table 2 (many):

UserID	Completed Date
1	5/9/2023 3:30:11 AM
2	6/13/2023 8:15:25 PM
3	8/12/2022 7:08:10 PM

I need to calculate, based on the user timezones, which completed dates happened after working hours for each user, consdering the end of the day at 6:00 pm.

Examples:

- UserID 2, lives in CST time (UTC -6). The user completed the task on 6/13/2023 at 8:15 PM (UTC),  which means the user completed the task at 2:15 pm (CST), which falls in working hours for the user.

- UserID 1, lives in PST time (UTC -8). The user completed the task on 5/9/2023 at 3:30 AM (UTC),  which means the user completed the task at 7:30 PM (PST of the previous day), which falls out of working hours for the user.

My challenge is to calculate which completed dates fall out of working hours.

Thanks for your support!

[rgalvez]

Hi Yolo!  This worked like a charm.  Thank you so much!

[v-xinruzhu-msft · Accepted solution]

Hi @rgalvez 

1.You can create a new table(TimeZone) as follows:

2.Then create a one-many relationship between timezone table and table 1

3.Then create a calculated column in table 2

IfOvertime =
VAR a =
    RELATED ( 'TimeZone'[Timedifference] )
VAR b =
    CONVERT (
        FORMAT ( 'Table 2'[Completed Date] - TIME ( a, 0, 0 ), "General Date" ),
        DATETIME
    )
VAR c =
    CONVERT ( DATEVALUE ( b ) & " " & TIME ( 18, 0, 0 ), DATETIME )
RETURN
    IF ( b > c, "Yes", "No" )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.