• 0

# Convert a datediff to dd:hh:mm

## Question

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

## Recommended Posts

• 0

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.

##### Share on other sites

• 0

Hi

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

##### Share on other sites

• 0

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

Formats (114) -

##### Share on other sites

• 0

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

##### Share on other sites

• 0

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

##### Share on other sites

• 0

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.

##### Share on other sites

• 0
On 10/3/2022 at 11:38 AM, Wikiwi said:

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

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

##### Share on other sites

• 0

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

##### Share on other sites

• 0

I used the exact code you listed above and:

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

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.