Approximate Date & Time Based on Condition

[JoshuaIdumijie]

ArrivalTime	Desired Results
1/30/2023 5:00	1/30/2023 8:00
1/24/2023 15:00	1/24/2023 15:00
1/27/2023 17:30	1/30/2023 8:00

Please, I need to create a column that achieves the result above. When the arrival time is before 8 AM on a weekday then the column should contain 8 AM same day but if the arrival time is later than 4 PM then it should contain 8 AM the next week day. But if the arrival time is on a weekend then it should contain 8 AM the next weekday. 

Weekend is Saturday and Sunday and Weekdays are Monday to Friday. 

[FreemanZ]

hi @JoshuaIdumijie 

you data table and description is not consistent. I tried following your description:

Result = 
VAR _date = FORMAT([ArrivalTime], "m/d/yyyy")
VAR _time = FORMAT([ArrivalTime], "h:mm")
VAR _time8 = TIME(8, 0, 0)
VAR _weekday = WEEKDAY(_date, 2)
VAR _nextweekday =
MINX(
    FILTER(
        CALENDAR( MIN(TableName[ArrivalTime]), MAX(TableName[ArrivalTime])),
        [Date]>VALUE(_date) && NOT WEEKDAY([Date], 2) IN {6, 7}
    ),
    [Date]
)
RETURN
IF(
    NOT _weekday IN {6,7} && VALUE(_time)<= _time8,  
    _date&" "&_time8, 
    _nextweekday&" "&_time8
)

it worked like this:

you may verify that with more data.