Skip to main content
3-Newcomer
April 3, 2025
Solved

Calculating duration between two date fields

  • April 3, 2025
  • 1 reply
  • 1118 views

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

 

Best answer by JosephM_SAI

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:

 

JosephM_SAI_0-1743707417448.png

JosephM_SAI_1-1743707433353.png

then this expression:

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

 

should work for you:

JosephM_SAI_2-1743707557653.png


 

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!

1 reply

12-Amethyst
April 3, 2025

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:

 

JosephM_SAI_0-1743707417448.png

JosephM_SAI_1-1743707433353.png

then this expression:

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

 

should work for you:

JosephM_SAI_2-1743707557653.png


 

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!

3-Newcomer
April 4, 2025

Of course!

 

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