Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Date\Time Difference Post Reply Post New Topic
Author Message
roxaneamanda
Newbie
Newbie


Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
Quote roxaneamanda Replybullet Topic: Date\Time Difference
     Posted: 20 Jan 2010 at 5:52am
Is there a way for me to calculate the difference between 2 dates and times using the hours of 7:30am to 1:30am?
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 20 Jan 2010 at 7:43am
of course there is...it is just more complex.  I remember a post on this from a while back, but the idea is:
 
get the number of hours or minutes between your two dates, then subtract the 6 hours for each day, and the number of days if you are closed on weekends.  Now you have the difference in say minutes from the start to the end, and then divide that number by the number of minutes between your open and close times.
 
This should at least get you close (you might want to account for work days or calendar days).
 
HTH
IP IP Logged
roxaneamanda
Newbie
Newbie


Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
Quote roxaneamanda Replybullet Posted: 20 Jan 2010 at 8:06am
Hi,
Thanks you very much for replying, however, I need some further assistance:
 
If I have a list like the following;
 
Date Created                   Date Closed
1. 18/02/10 07:30am           19/02/10 09:00am
2. 19/02/10 09:45am           19/02/10 17:45am
3. 19/02/10 07:30am           20/02/10 01:30am
4. 19/02/10 17:30pm           20/02/10 15:00pm
 
The answers should read [h]:mm:ss (based on operating hours 365 days 7:30am to 1:30am which equates to an 18 hour working day)
1. 19:30:00
2. 08:00:00
3. 18:00:00
4. 15:30:00
 
If I use your suggestion, how on earth do I build this into a formula??!?
 
Confused


Edited by roxaneamanda - 20 Jan 2010 at 8:25am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 20 Jan 2010 at 9:36am
1) calculate the datediff based on minutes.
2) if the close time >1:30 am
  a) subtract (6*60)*datediff in days
 
should get you close.  Since it is 365/year most other issues drop away. Number of days might be tricky...if opened at 12:01 am and closed at 9:01 on the same day, the datediff in days =0, similiarly opened at 11:59pm and closed at 10:59 am the next day is 1 day diff, so it should work correctly, but you will have to check / code for the crossing of boundary times.
 
HTH
IP IP Logged
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