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...
0 RuisiHansamu Posted October 2, 2022 Report Share Posted October 2, 2022 Hi, you may also want to consider this formula with HH:MM format : CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])/60)) +':'+ CASE WHEN (Datediff(minute,[@field:Start_date],[@field:End_date])%60) < 10 THEN '0'+CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])%60)) ELSE CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])%60)) END you may also find this helpful: https://howto.caspio.com/datapages/reports/advanced-reporting/calculated-fields-and-datediff-function/ Leon13 1 Quote Link to comment Share on other sites More sharing options...
0 Leon13 Posted October 2, 2022 Report Share Posted October 2, 2022 Hi @RuisiHansamu - Incredible timing as I am searching for this exact formula at this time. I tried your referenced formula to calculate DD:HH:MM from Ticket Open: [@field:Submission_Date] through closure [@field:Time_to_Resolve] I am using : 09/22/2022 06:14:52 as [@field:Submission_Date] 10/02/2022 06:39:24 as [@field:Time_to_Resolve] The result I get is: 240:27 Any thoughts please? Below is the entire formula from using your recommendation: CONVERT(VARCHAR,(Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])/60)) +':'+ CASE WHEN (Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60) < 10 THEN '0'+CONVERT(VARCHAR,(Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60)) ELSE CONVERT(VARCHAR,(Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60)) END Quote Link to comment Share on other sites More sharing options...
0 Wikiwi Posted October 3, 2022 Report Share Posted October 3, 2022 @Leon13 Try changing the first line of code to this one: CONVERT(VARCHAR,(Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve]))). It should show the correct difference of days now. Quote Link to comment Share on other sites More sharing options...
0 Leon13 Posted October 4, 2022 Report Share Posted October 4, 2022 On 10/3/2022 at 11:38 AM, Wikiwi said: @Leon13 Try changing the first line of code to this one: CONVERT(VARCHAR,(Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve]))). It should show the correct difference of days now. @Wikiwi Thank you for your guidance! Below is the new code, here is what I see: [@field:Submission_Date] = 9/02/2022 20:18:34 [@field:Time_to_Resolve] = 10/02/2022 22:00:55 Results: 32:42 Appreciate any additional guidance Thank you! New code: CONVERT(VARCHAR,(Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve])))+':'+ CASE WHEN (Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60) < 10 THEN '0'+CONVERT(VARCHAR,(Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60)) ELSE CONVERT(VARCHAR,(Datediff(minute,[@field:Submission_Date],[@field:Time_to_Resolve])%60)) END Quote Link to comment Share on other sites More sharing options...
0 Wikiwi Posted October 5, 2022 Report Share Posted October 5, 2022 @Leon13 Not sure why you are getting 32 for the day part. I tried the values you used for testing and it shows me 30. I also edited hours part for the formula since that part is incorrect. Code I used: CONVERT(VARCHAR,(Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve])))+':'+ CASE WHEN (Datediff(hour,[@field:Submission_Date],[@field:Time_to_Resolve]) % (24*Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve]))) < 10 THEN '0'+ CONVERT(VARCHAR,Datediff(hour,[@field:Submission_Date],[@field:Time_to_Resolve])%(24*Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve]))) ELSE CONVERT(VARCHAR,Datediff(hour,[@field:Submission_Date],[@field:Time_to_Resolve])%(24*Datediff(day,[@field:Submission_Date],[@field:Time_to_Resolve]))) END Screenshot of my DataPage: Quote Link to comment Share on other sites More sharing options...
0 Leon13 Posted October 5, 2022 Report Share Posted October 5, 2022 @Wikiwi Big thank you for your response and any additional guidance. I used the exact code you listed above and: 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
9 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.