Forum Discussion

DeepakJha23's avatar
DeepakJha23
Helper I
6 years ago

Time slots calculation between time ranges

I need some help regarding an issue that i am facing.

I have a column Tag that represents a machine. And they have a RA_Time(start time) and RT_Time(Runs till/end time) with a schedule of Daily(runs Daily based on weekday/weekend),Hourly(runs Hourly in a day based on weekday/weekend) and Weekly (runs only once based on On Day) .
We are trying to see Tag wise in a day the time slot a machine was free in 24 hours.

Let say Machine 7 runs daily on weekdays from 4:15 pm to 4:45 pm(Daily), 5:00 p.m to 5:30 p.m(Weekly -only on Sunday),6:00 pm to 6:00 am(Daily - till next day) .

The free time slots today(Thursday) in a 24 hrs format would be :

Free slot on Thursday - 12:00 AM - 4:15 PM , 4:45 PM - 6:00PM
Free slot on Sunday - 12:00 AM - 4:15 PM,4:45 PM - 5:00 PM, 5:30 PM - 6:00 PM

I am attaching the screenshot containing the data rows.

Any help on the above would be very appreciated.

 

amitchandak Greg_Deckler parry2k MFelix 

 

Thanks,

Deepak

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I would create a time/ date table with [Time& Date} collumn

    Then use If statement  to create a new custom column [ STatus] = IF [Time& Date] >= [Start Date] && [Time&Date] <= [Finish Date]  then "Busy" else "Free"

    Then you could filter by first and last to give you the ranges . With this solution you would need to split the tables (per tag). 

    There probably is a cleaner and more elegant way of doing this. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi DeepakJha23 ,

     

    If i understand you correctly,

    1# Use DATEDIFF() function to calculate the time ranges per row.

    2# Sum up the time ranges with same tags per day. You might use ALLEXCEPT() function.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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