cancel
Showing results for 
Search instead for 
Did you mean: 

AGED TIME FROM TWO DATETIME FIELDS

Former Member
0 Kudos

SAP BusinessObjects BI Platform 4.1 Support Pack 1

Version: 14.1.1.1036


I am trying to find a way to subtract datetime to get average time.  The commands I have don't calculate correctly. 

I've seen a lot of posts on this topic but none on my problem.



thanks

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

Hi,

First you needs to convert the date & time in seconds and then subtract.

Var Diff in seconds=DaysBetween([Incident Reported Date Time] ;[Incident Last Resolved Date Time]) * 86400

+

(

ToNumber(FormatDate([Incident Last Resolved Date Time] ;"HH")) * 3600 +

ToNumber(Left(FormatDate([Incident Last Resolved Date Time] ;"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Incident Last Resolved Date Time] ;"ss"))

)

-

(

ToNumber(FormatDate([Incident Reported Date Time] ;"HH")) * 3600 +

ToNumber(Left(FormatDate([Incident Reported Date Time];"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Incident Reported Date Time] ;"ss"))

Later use this formula to convert seconds in the day.

=FormatNumber(Floor([Var Diff in seconds]/86400);"00") + "Days" +"."

FormatNumber(Floor(Mod([Var Diff in seconds ;86400)/3600);"00")

Amit

Former Member
0 Kudos

Amit,

Thank you so much.  This solution worked as designed, thanks again.

There was only one error in the formula missing an extra ")" at the end of the first object.

Easy enough to figure out.

Answers (0)