Forum Discussion

Keston's avatar
Keston
Frequent Visitor
1 year ago

Working Hours Between two date/time fields

Hello,   Seeking the assistance of the forum, I have two date/time fields in the following format and the following script to calculate the working hours between 8 - 1700 excluding weekend. The scr...
lbendlin's avatar
lbendlin
Super User
1 year ago

You can choose to ignore these rows, or replace the blanks with an appropriate date. Not sure if "Today"  is appropriate for this?

Keston's avatar
Keston
Frequent Visitor
1 year ago

Hey lbendlin,

 

So I added Now() to the blank field and this worked well.

However, now I observed the root of the issue, I have a few instances were the created date is later than the assigned date, I'm not sure why this would have occured.

But, do you have any recommendations to the script to exlucde these instances?

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    You could use ABS() to always get a positive duration.

  • Keston's avatar
    Keston
    Frequent Visitor
    1 year ago

    Ok noted, just to be clear, I should replace CALENDAR() with ABS()?

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
    var workingdays = FILTER(CALENDAR(MIN([Created],[Assigned]),MAX([Created],[Assigned])),WEEKDAY([Date],2)<6)