Convert a datediff to dd:hh:mm

[KAPITYAN]

I have a formula field in one of my tables that calculates the difference between to date/time fields. I want the results to display as dd:hh:mm.

Here is my formula as it is currently. Any suggestions would be appreciated.

CASE
WHEN [@field:TimeAssigned] Is Null
THEN ''
ELSE Datediff(minute, [@field:Time], [@field:TimeAssigned])
END

[NailDyanC]

To achieve your desired result, please try to use this formula:

CASE
WHEN [@field:TimeAssigned] Is Null
THEN ''
ELSE
CAST((DATEDIFF(HOUR, [@field:Time], [@field:TimeAssigned]) / 24) AS VARCHAR)
+ ':' +
CAST((DATEDIFF(HOUR, [@field:Time], [@field:TimeAssigned]) % 24) AS VARCHAR)
+ ':' +
CASE WHEN DATEPART(SECOND, [@field:TimeAssigned]) >= DATEPART(SECOND, [@field:Time])
THEN CAST((DATEDIFF(MINUTE, [@field:Time], [@field:TimeAssigned]) % 60) AS VARCHAR)
ELSE
CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, [@field:TimeAssigned]), [@field:TimeAssigned]) % 60)
AS VARCHAR)
END
+ ':' + CAST((DATEDIFF(SECOND, [@field:Time], [@field:TimeAssigned]) % 60) AS VARCHAR)
END

The output will be dd:hh:mm:ss.

[NailDyanC]

Hi @KAPITYAN,

Just to add from my previous comment above, you may try use this one as well:

CASE
WHEN [@field:TimeAssigned] IS NULL
THEN ' '
ELSE CONVERT(VARCHAR(MAX),(Datediff(day, [@field:TimeAssigned], [@field:Time]))) + CONVERT(varchar(5), DATEADD(minute, DATEDIFF(MINUTE,[@field:TimeAssigned], [@field:Time]), 0), 114)
END

[ParkLoey]

Hi! Just wanted to add a few links that are helpful with this case. 

Function reference - https://howto.caspio.com/function-reference/

Formats (114) -