Forum Discussion

JoshuaIdumijie's avatar
JoshuaIdumijie
Regular Visitor
3 years ago

Approximate Date & Time Based on Condition

ArrivalTimeDesired Results
1/30/2023 5:001/30/2023 8:00
1/24/2023 15:001/24/2023 15:00
1/27/2023 17:301/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. 

 

Need Help

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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.