Please correct my cycle days formula

I want to calculate the no of cycle days between ResolveDate and CreateDate with decimal num. My createdate and resolvedate have value of date with hours. So I need exact count like if I resolve a SIM in one and half day then it need to show cycle days as 1.5 by excluding the weekend days. So I try to create below formula which is showing error and correct my formula if it is wrong.

(
(
datediff(
dateadd({Resolvedate}, 1, ‘day’),
dateadd({Createdate}, -1, ‘day’),
‘HH’
)
)
-
(
24 * count(
filter(
sequence(
dateadd({Createdate}, 1, ‘day’),
dateadd({Resolvedate}, -1, ‘day’),
1
),
(date) => extract(“dayofweek”, date) = 1 or extract(“dayofweek”, date) = 7
)
)
)
) / 24.0

1 Like

Hello @hripraa, I think in this scenario, the aggregation for netWorkDays would be the best option to get your expected result.

You could use this to get the number of non-weekend days between Createdate and Resolvedate, then you could use the extract function to get the hours of the day for create date, subtract it from 24, then divide it from 24 and add it to your result.

The 2nd part of the calculated field would look something like this:
(24 - extract('HH', {Createdate}))/24

Let me know if that helps!

1 Like

Hi @hripraa, I am aligned with the answer DylanM gave.

I believe you could use a formula like below (Note there are more than one way to approach the calculated field, and even the solution below could potentially be optimized more):

ifelse(
// 1.0) If "Createdate" day is before the "Resolvedate" day then:
    extract("DD",{Createdate})<extract("DD",{Resolvedate}), 
        ifelse(
        // 1.1) If the hours (ignoring days) are equal to or more than 24 hours apart then get the number of days between the dates, and add the "overflow hours". EX: C=2024-01-01 01:00; R=2024-01-02 03:00 ->  (2-1 = 1 day and 3-1 = 2 hours of overflow)
            extract("HH",{Createdate})<=extract("HH",{Resolvedate}),(netWorkDays({Createdate},{Resolvedate})-1)+((extract("HH",{Resolvedate})-extract("HH",{Createdate}))/24),

        // 1.2) Otherwise get the number of days between the dates but remove 1 day, add how many hours were left of the "createdate" day, and how many hours have occured on the "Resolvedate" day. EX: C=2024-01-01 02:00; R=2024-01-03 1:00 -> (3-1-1 = 1 day and 22+1 = 23 hours)
            extract("HH",{Createdate})>extract("HH",{ResolveDate}), (netWorkDays({Createdate},{Resolvedate})-2) + ((extract("HH",{Resolvedate})+(24-extract("HH",{Createdate)))/24),

        // 1.3) Else NULL
            NULL
        ),

// 2.0) If "createdate" day is the same as the "Resolvedate" day then only get the difference between the hours EX: C=2024-01-01 01:00; R=2024-01-01 03:00 -> (2 hours)
    extract("DD",{Createdate})=extract("DD",{Day 2}), dateDiff({Createdate},{Resolvedate},"HH")/24,

 // 3.0) Else NULL   
    NULL
    )

A couple of things I would note:

  1. How do you handle createdate and resolvedate that occur on the weekend, if it is possible?

    • I noticed in your solution you used createdate = createdate - 1 and resolvedate= resolvedate + 1. The nice thing about that is if you use the addWorkDays function to perform those updates then it will automatically push the dates to a day of the week:
      addWorkDays(createdate,-1)
      addWorkDays(resolvedate,1)

      • EX: R= 2024-01-06 02:00 (Saturday) → 2024-01-08 00:00 (Monday)
      • EX: C= 2024-01-07 08:00 (Sunday) → 2024-01-05 00:00 (Friday)
    • Otherwise you may need to define how to handle events that occur on the weekend

      • For example you could push anything that occurs on the weekend to monday at midnight with an equation like below:
      ifelse(
       extract("WD",createdate)= 7, addDateTime(2,"DD",truncDate("DD",createdate)),//If occurred on Saturday, then set date to Mondy at midnight
       extract("WD",createdate)= 1, addDateTime(1,"DD",truncDate("DD",createdate)),//If occurred on Sunday, then set date to Monday at midnight
       createdate// Otherwise keep the same
      )
      
  2. The netWorkDays function is inclusive of both dates. So if both the createdate and resolvedate occurred on the same day it would return 1, even though the events are less than 24 hours apart. That is why in the equation mentioned above we -1 after the netWorkDays function.

1 Like