Date and Time Calculation

[keenoafc]

I have a need to create a formula which can do the following in Crystal:

Calculate Current Date/Time minus Submit Date Time - This bit Im good with. However i only want it to calculate for Mondays, Tuesdays, Wednesdays, Thursdays or Fridays and only between the hours of 08:00am and 21:30pm.

For example an Order was submitted at 14:00pm on Friday 5th April and the current date/time is 10:00am on Tuesday 9th April. I need the formula to be able to calculate only:
- The 7.5 hours fopr the Friday
- The 13.5 Hours for the Monday
- The 2 Hours for the Tuesday
Total of 23 Hours.

How can i write a formula to exclude any time after 21:30pm at night and before 08:00am each morning and also exclude all weekends so from 21:30pm on Fridays to 08:00am on Mondays?

Really appreciate any help or guidance I am being given.

Reporting using Crystal X1 from a SQL Database.

Many Thanks

 

[briangriffin]

whileprintingrecords;
datetimevar v_current := currentdatetime;
datetimevar v_datecounter := {SubmitDateTime};
numbervar v_minutes := 0;

while v_datecounter < v_current
do
(if time(v_datecounter) in [time("08:00") to time("21:30")]
and not(dayofweek(v_datecounter) in [7,1])
then
v_minutes := v_minutes + 1
else
v_minutes;

v_datecounter := dateadd("n",1,v_datecounter));

(v_minutes/60)


This returns 23.03, so you'll probably want to round, truncate, floor, whatever for your needs.

Also, you'll need to validate this with several different submit/current datetime combinations - I'm not swearing it's perfect

 

[amyceres]

Question would you be taking holidays into consideration?

 

[keenoafc]

Thank you for your help, this has been great.

THis returns the values but not as propoer Hrs and Minutes. For example on some of them it returns 139.73.

What do I need to do to convert this number to 'Real' Hrs and Minutes??

Many Thanks

 

[briangriffin]

Try this:

totext(floor({@Minutes}/60),"#",0) + " Hours, " + totext({@Minutes} mod 60,"#",0) + " Minutes