cancel
Showing results for 
Search instead for 
Did you mean: 

Exclude duplicate or overlapping data when adding time fields

Former Member
0 Kudos

Post Author: Mark O

CA Forum: Formula

I am using Crytsal Reports XI. I have a several rows of start times, end times and total minutes. I would like to get a total time but some of the times are duplicates or overlapping each other. These duplicates/overlaps are not errors; they need to be in the report. I just can't double count the time. I am seeking a result of 45 Total Min in the below example. Thanks.

Start Time

End Time

Total Min

2:45 PM

3:15 PM

30

3:00 PM

3:15 PM

15

3:00 PM

3:30 PM

30

75

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Formula

I'm not sure if your example is misleading!

For what you've shown a simple calculation between the start time of the first record and the end time of the last record gives your answer (assuming an ascending sort on start time, end time). However, this might just be because there's always an overlap in your example data.

Another solution might be to compare the start time of each record with the end time of the previous record, and choose the appropriate one of these as a new 'start time' for each record. But this might just be because the end times in your example data are always >= the end time of the previous record.

Is the following example possible? Would you want the final answer to be 50?

Start Time

End Time

Total Min

2:45 PM

3:15 PM

30

3:20 PM

3:40 PM

20

3:25 PM

3:35 PM

10

Former Member
0 Kudos

Post Author: sharona

CA Forum: Formula

try using if <> next then count else 0 something like that may work.