Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Crystal Reports - overlapping datetimes

Crystal Reports - overlapping datetimes

I would like to create formula, that finds overlapping times within one date interval for excample:
datetime_in - datetime out
11.07.2017 08:00 - 11.07.2017 09:00
11.07.2017 08:20 - 11.07.2017 10:00
11.07.2017 10:10 - 11.07.2017 14:20
11.07.2017 12:00 - 11.07.2017 14:25
12.07.2017 08:00 - 12.07.2017 09:00
12.07.2017 08:20 - 12.07.2017 08:40
12.07.2017 10:00 - 12.07.2017 14:30
12.07.2017 12:00 - 12.07.2017 14:45

Formula corrected datetime_in - corrected datetime_out
11.07.2017 08:00 - 11.07.2017 09:00
11.07.2017 09:00 - 11.07.2017 10:00
11.07.2017 10:10 - 11.07.2017 14:20
11.07.2017 14:20 - 11.07.2017 14:25
12.07.2017 08:00 - 12.07.2017 09:00
12.07.2017 09:00 - 12.07.2017 09:00
12.07.2017 10:00 - 12.07.2017 14:30
12.07.2017 14:30 - 12.07.2017 14:45

I have grouped data first by date(month) and second by employee id so ill get a report that displays record for each employee in separate page. Now i need total time that employee recorded within a day without any overlapping times.

RE: Crystal Reports - overlapping datetimes

Replace {table.datetime_in} with a formula {@datetimein}:

If onfirstrecord or
{table.employeeID} <> previous({table.employeeID}) then
{table.datetime_in} else
If {table.datetime_in}<previous({table.datetime_out}) then
Previous({table.datetime_out}) else

To tally the time per day, I would insert another group on datetime on change of day. Then create three formulas:

//{@reset} to be placed in the day group header:
Numbervar daysum;
Daysum := 0

//{@accum} to be placed in the detail section:
Numbervar daysum;
Daysum := daysum + datediff("n",{@datetime_in},{table.datetime_out});//n=minutes

//{@display} to be placed in the day group footer:
Numbervar daysum;

This would give you the sum in minutes. You could then convert the sum to hours, if you wish by adding a final line to the display formula with:



RE: Crystal Reports - overlapping datetimes

Thank you for the answer.
Ive tried those formulas, but theres 1 problem - this formula limits to only 1 row, if i have overlap in more than one row then it doesnt get changed:
datetime in - datetime out
12.07.2017 08:00 - 12.07.2017 14:00
12.07.2017 09:00 - 12.07.2017 10:00 - this will get changed
12.07.2017 11:00 - 12.07.2017 14:00 - this will not, because theres no overlap with this row datetimes vs. previous row datetimes

In excel i did an array formula that looked if emp_id=emp_id and date_in=date_in and then i looked for overlapping times for date range and if
overlapping occured("TRUE") then i found min(ie. start) and max(ie. end) and printed the results.
(well, sumproduct formula that i used isnt perfect because if Time_in was same time as Time_out, then it also marked that record "TRUE", i have to limit the formula +-1minute)
*attached sumproduct formula picture*

now back to this formula
Lets say well have about 20 overlapping datetimes in a day then i have to create 20 different datetimes_in's and datetimes_out's to find all overlaps,
but is it possible to not only look "previous" but to look all datetimes withing a range (day)?

RE: Crystal Reports - overlapping datetimes

Okay, I see the issue: Try this to replace the datetime_in:

datetimevar dtin;
datetimevar dtout;

If onfirstrecord or
{table.Employee_ID} <> previous({table.Employee_ID}) or
date({table.datetime_in})<>date(previous({table.datetime_in})) then (
dtin := {table.datetime_in};
dtout := {table.datetime_out}
) else
If {table.datetime_in} < dtout then (
dtin := dtout;
if {table.datetime_out}>=dtout then
dtout := {table.datetime_out} else
dtout := dtout
) else
dtin :={table.datetime_in};
dtout := {table.datetime_out}

If you want to display a corrected datetime_out, then use a formula like to replace the out field.

datetimevar dtout;

Change {@accum} to:
Numbervar daysum;
datetimevar dtin;
datetimevar dtout;

Daysum := daysum + datediff("n",dtin,dtout);//n=minutes


RE: Crystal Reports - overlapping datetimes

Thank you lbass, you are a genius!
Your formula is perfect! Thank you again!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close