Date and Time difference between date range

[former_member603578]

Greetings All,

I am required to calculate the difference between two dates and two times in HH:MM:SS format.

a.In my infoprovider I have only Date and Time as keyfigures.

b.An indicator I and D differentiates as Start Date (indicator = I) and End Date (indicator =D), similarly start time and end time. This was done in BEx.

Now, I need to calculate the difference between two dates and times in HH:MM:SS format.

I have tried the TIME() function as below and it is giving me wrong results.

=TIME((End date - Start Date)*86400)) + TIME(( End Time - Start time))

Kindly share your inputs. Thanks in advance.

[former_member183777]

HH:MM:SS format represents time and not time difference. So please recheck your requirement. You can get time difference in seconds using your approach without using the Time function

[former_member603578]

Hi Vineet,

My mistake . Need Difference in Days,hours and minutes.

[former_member183777]

First compute the difference in seconds, then use div and mod functionsto compute days hours and secs

(difference ) div (number of seconds in a day) will give number of days, use mod instead of div to get remainder seconds and then repeat to find hours and seconds

compute total diff in sec will need some work as well. If your current formula doesn't give you the right diff in secs You can try the following

((end date - 1/1/1000)+(end time -00:00:00)) - ((start date -1/1/1000)+(start time -00:00:00))

basically compute mute the diff from beg of time from end date and from start date and then take the diff of two differences