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) - 

 

[RuisiHansamu]

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]

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 

 

[Wikiwi]

@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.

 

[Leon13]

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 

[Wikiwi]

@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:

[Leon13]

@Wikiwi Big thank you for your response and any additional guidance.

I used the exact code you listed above and: