Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: DateDiff -> HH:MM:SS Post Reply Post New Topic
Author Message
NCGOV
Newbie
Newbie
Avatar

Joined: 04 Oct 2016
Online Status: Offline
Posts: 24
Quote NCGOV Replybullet Topic: DateDiff -> HH:MM:SS
    Posted: 16 Mar 2018 at 4:19am
Formula to calculate the difference in HH:MM:SS between two date time fields.

How many Hours, Minutes, Seconds worked

{unit.onduty} {unit.offduty}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Mar 2018 at 6:08am
you have to do a datediff using seconds as the increment then you can use a formula field to change result of total seconds to display like HH:MM:SS.
There are a ton of those formulas posted already.
IP IP Logged
NCGOV
Newbie
Newbie
Avatar

Joined: 04 Oct 2016
Online Status: Offline
Posts: 24
Quote NCGOV Replybullet Posted: 16 Mar 2018 at 6:38am
This is the formula(s) I have

CStr(Round(DateDiff ("n", {unit.onduty}, {unit.offduty}) \ 60 ),0) +':'+
CStr(Round(DateDiff ("n", {unit.onduty}, {unit.offduty}) MOD 60 ),0)

AND/OR

DateDiff ("H", {unit.onduty}, {unit.offduty})

What do I need to do differently to make it display as HH:MM:SS?


IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Mar 2018 at 7:41am
if you never can exceed 24 hours try

time(dateadd('n',(DateDiff ("n", {unit.onduty}, {unit.offduty})),datetime(1900,1,1,0,0,0)))
and set the result to display as military time
IP IP Logged
NCGOV
Newbie
Newbie
Avatar

Joined: 04 Oct 2016
Online Status: Offline
Posts: 24
Quote NCGOV Replybullet Posted: 16 Mar 2018 at 9:01am
This formula seems to work....

TimeValue ({unit.offduty}-{unit.onduty})


Edited by NCGOV - 16 Mar 2018 at 9:09am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Mar 2018 at 9:22am
what are some example values inside {unit.offduty} and {unit.onduty}
IP IP Logged
NCGOV
Newbie
Newbie
Avatar

Joined: 04 Oct 2016
Online Status: Offline
Posts: 24
Quote NCGOV Replybullet Posted: 16 Mar 2018 at 9:47am
Just date and time ...03/16/18  08:00:00
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.