KAPITYAN Posted September 12, 2020 Report Share Posted September 12, 2020 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 Quote Link to comment Share on other sites More sharing options...
0 NailDyanC Posted September 12, 2020 Report Share Posted September 12, 2020 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. Quote Link to comment Share on other sites More sharing options...
0 NailDyanC Posted September 19, 2020 Report Share Posted September 19, 2020 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 Quote Link to comment Share on other sites More sharing options...
0 ParkLoey Posted December 28, 2020 Report Share Posted December 28, 2020 Hi! Just wanted to add a few links that are helpful with this case. Function reference - https://howto.caspio.com/function-reference/ Formats (114) - Quote Link to comment Share on other sites More sharing options...
Question
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
Link to comment
Share on other sites
3 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.