Calculating duration between two date fields

[TJ_12890088]

I have a custom field for Planned start date and another for Planned finish date.

I would like to have another field that computes and displays the value of the time between the two dates as hours or minutes.

I'm pulling my hair out trying to get this working...I've gone over and over the documentation, but cannot get it right.  I think I'm close, but am missing something silly.

I've got the third custom field to calculate the time between two fixed dates(the date actually in the formula, rather than strings), no issue.  The issue is when trying to use the 2x custom fields date variables - 

I'm using this:

timeBetween (Date(customField[0]),Date(customField[1]), "minutes")

but an error appears saying 'unable to parse date [and then shows the CURRENT date/time] ' which would suggest it's not using the customField variables?

Am I doing something stupid?

Any help appreciated,

Thanks

[JosephM_SAI · Accepted solution]

These Computed Field Expressions can be quite tricky - and even trickier to debug - and 9 times out of 10 it does end up being something silly I didn't read exactly as it was intended, so I feel your pain!

---

So the right computed expression for what you want depends on the types of the two fields.

If both of the fields are of type Date, like so:

then this expression:

timeBetween(customField[1], customField[2], "minutes")
// 1 and 2 in my particular tracker

should work for you:

---

I was able to replicate your error of "unable to parse date [valid-looking date]" with your expression, and it happens because we're passing something that has a value of type Date - the content (value) of the custom field - to the function named Date, but Date the function expects a Strins (text) to be passed to it - and for them to be in a particular format.

So when it get's something that is not a String (text), Codebeamer does it's best and converts the non-String (in this case a Date) to a String (text) - which when a Date is converted to a String (text), it defaults to this format:

EEE MMM dd HH:mm:ss zzz yyyy
// "Fri Apr 04 00:00:00 UTC 2025" for example

which unfortunately not a valid format for Date the function, which for absolute dates only accepts this format: 

yyyy-MM-dd[ HH:mm[:ss]]
// "2025-04-04" or "2025-04-04 00:00" or "2025-04-04 00:00:00" for valid examples

Of course if the fields are of type Text, then what you have should work, but based on what you have and the fact it's not working, I'd doubt that's the case.

---

Whenever I have concerns or doubts about the incorrect field being used, I attempt accessing them via their Label, be that via their camelCased versions:

timeBetween(plannedStart, plannedFinish, "minutes")

or via their literal versions:

timeBetween(this["Planned Start"], this["Planned Finish"], "minutes")

---

Hopefully that works for you - or at least gets you closer to getting this computed field working for you! If not, feel free to reply with any additional details and I'm sure we'll get it figured out!

[TJ_12890088]

Of course!

Thank you so much, that is exactly what I was looking for and it seems obvious now - thanks again!

[TJ_12890088]

...whilst you're here....can I ask a follow up question.... 🙂

returning minutes is great- but is there a way to present a more user friendly output - for example, "0 days, 12 hours, 13 minutes" ?

[JosephM_SAI]

There is an official support article answering this very question:

How to make the custom Field value display like the format of "XX Weeks XX Days XX Hours XX Mins"“ when using the Compluted Formula like "timeBetween(customField[1],customField[2],"m")” in Codebeamer

---

If you did prefer not using multiple computed fields like suggested above and were fine with a slightly more complex expression, this would also work:

// If either plannedStart or plannedFinish is empty, return "Missing Dates", otherwise build the desired string
empty plannedStart || empty plannedFinish ? "Missing Dates" : concat(
    timeBetween(plannedStart, plannedFinish, "days"), " days, ",
    timeBetween(plannedStart, plannedFinish, "hours") % 24, " hours, ",
    timeBetween(plannedStart, plannedFinish, "minutes") % 60, " minutes"
)

 ...and if you wanted it to only show the units that had a value - `1 day, 30 minutes`, instead of `1 day 0 hours 30 minutes` for example - it's doable, but starts getting even more complex within a single expression:

// If either plannedStart or plannedFinish is empty, return "Missing Dates", otherwise build the desired string
empty plannedStart || empty plannedFinish ? "Missing Dates" : concat(
    // If number of days is greater then 0, include "X days" in the output, otherwise don't
    timeBetween(plannedStart, plannedFinish, "days") > 0 ? concat(timeBetween(plannedStart, plannedFinish, "days"), " days") : "",
    
    // Add a comma separator if days are included AND the remaining hours are greater than 0
    timeBetween(plannedStart, plannedFinish, "days") > 0 && timeBetween(plannedStart, plannedFinish, "hours") % 24 > 0 ? ", " : "",
    
    // If number of hours - after accounting for full days - is greater than 0, include "X hours" in the output, otherwise don't
    timeBetween(plannedStart, plannedFinish, "hours") % 24 > 0 ? concat(timeBetween(plannedStart, plannedFinish, "hours") % 24, " hours") : "",
    
    // Add a comma separator if either (days OR hours are included) AND the remaining minutes are greater than 0
    (timeBetween(plannedStart, plannedFinish, "days") > 0 || timeBetween(plannedStart, plannedFinish, "hours") % 24 > 0) && timeBetween(plannedStart, plannedFinish, "minutes") % 60 > 0 ? ", " : "",
    
    // If number of minutes - after accounting for full days and hours - is greater than 0, include "X minutes" in the output, otherwise don't
    timeBetween(plannedStart, plannedFinish, "minutes") % 60 > 0 ? concat(timeBetween(plannedStart, plannedFinish, "minutes") % 60, " minutes") : ""
)

---

Of course these single expressions only gets more daunting the more you try to get them do...hence the support article guiding one through creating additional computed fields as needed, allowing for reuse and a simplified debugging experience.

[TJ_12890088]

thank you very much, that's great.  I'm just starting with codebeamer, so I thank you all for your assistance.