Date Time Calc with date & time exclusions 1

[gpwizz]

HELP !!!!
I am trying to create a formula but am struggling and hope someone can help me.
I basically have a start date/time and a downtime where the downtime is the amount of hours (in decimal format) that the problem has been unresolved, this is often different to the close date/time. I need to add the downtime to the start date/time which I can do fairly easily. The problem really comes because I am operating within service windows, for example Monday to Friday, 9am to 5pm. If the start date/time was 1st September @ 4pm and the downtime was 5 hours I would like the result to show 2nd September 1pm. Would appreciate any help.

 

[synapsevampire]

You have 2 things to consider, business days (this means including holidays, how do you intend to handle that?), and adding the hours.

for x := .1 to {table.hours} step .1 do(
// add the time at 6 minute increments
if dayofweek(dateadd("n",6,currtime)) in 2 to 6
and
hour(dateadd("n",6,currtime)) <= 17 then
currtime:=dateadd("n",6,currtime)
else
// add a day if a workday and over 5 pm
if dayofweek(dateadd("n",6,currtime)) in 2 to 5
and
hour(dateadd("n",6,currtime)) > 17 then
currtime:=cdatetime(year(currtime),month(currtime),day(currtime),9,0,0)+1
else
// go to Monday if Friday and over 5 pm
if dayofweek(dateadd("n",6,currtime)) = 6
and
hour(dateadd("n",6,currtime)) > 17 then
currtime:=cdatetime(year(currtime),month(currtime),day(currtime),9,0,0)+3

I can't test right now, but this looks close. I'm not certain if the step clause accepts the .1, if not, multipy your hours by 10.

If your decimal has a 2 digit precision, we'll have to adjust the logic, but since you didn't post an example...this should get you close, in the future try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[mbarron]

Here's a quickie that will work assuming that you'll not have downtime greater that 8 hours. I've also added a "wrap to Monday".

datetimevar startdown;
datetimevar uptime1;
datetimevar uptimeout;
numbervar down:={down.time};

startdown:={datetime.down};
uptime1:=startdown + down/24;
if hour(uptime1)>16 then uptimeout:=dateadd("h",16,uptime1)
else uptimeout:=uptime1;

if dayofweek (uptimeout)>6 then uptimeout:=uptimeout+2 else
uptimeout




Mike

 

[gpwizz]

Sorry for not putting more info in. I am using Crystal 8.0 running from an SQL database. Example data is as follows.
Call in date = 01-09-04 13:27
Downtime = 18.55 (works out to 66780 seconds/18hrs 33mins)
I would expect a result, based upon a 9 to 5 window off approximately 03-09-04 16:00.
Just to confirm, the Call in date is a database field called SCCall.CallInDate and the Downtime is called SCCall.CallDTime.

Synapsevampire - Sorry, couldn't even get started with your one, didn't really understand enough of it probably due to my lack of information.

mbarron - got yours to work nicely however as you mentioned only for downtimes less than 8 hours. Any ideas?

Also need to exclude holidays but have a holiday.txt file that I can use if you can suggest anything.

Many thanks for your prompt replies.

George

 

[mbarron]

Try this one (It doesn't deal with holidays - I'd have to do more manipulating):

local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate({test.down}/8);
local numbervar down:={test.down}- (daysadd * 8);
local numbervar looper;
local numbervar addto;

uptime1:={test.date} + down/24;

if time((uptime1))>time(17,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);

uto:=startdown;

for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+1;daysadd:=daysadd+1) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;




Mike

 

[gpwizz]

Thanks Mike, I have added that in and am still getting a
couple of errors. My formula is now:

local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate({SCCall.Call_InDate}/8);
local numbervar down:={SCCall.Call_InDate} - (daysadd * 8);
local numbervar looper;
local numbervar addto;
uptime1:={SCCall.call_InDate} + down/24;
if time((uptime1))>time(17,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);
uto:=startdown;
for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+1;daysadd:=daysadd+1) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;

My field SCCall.Call_InDate is the call log date and time so not sure if that is the correct field to enter. I am getting an error against line 4 "A number, or currency amount is required here" Any ideas? Also can you check the rest of my field names I have entered as I suspect they are wrong. If you could clarify what information I should be entering I can then find the right field or formula.

If you could email me it would make it a little easier. My email address is george.purser@isgwebb.com

Thanks
George

 

[mbarron]

The field for these two lines should be your amount of down time.

local numbervar daysadd:=truncate({SCCall.Call_InDate}/8);
local numbervar down:={SCCall.Call_InDate} - (daysadd * 8);


Mike

 

[gpwizz]

Thanks Mike, works like a dream. As you can tell I am new to Crystal, have managed to do ok so far but just got stumped with this one. Your help is greatly appreciated.

 

[gpwizz]

Mike
Found an example where this doesn't work and cannot figure it out, wonder if you can. I have the following formula which works in most cases.

local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate({SCCall.Call_DTime}/8);
local numbervar down:={SCCall.Call_DTime} - (daysadd * 8);
local numbervar looper;
local numbervar addto;
uptime1:={SCCall.call_InDate} + down/24;
if time((uptime1))>time(23,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);
uto:=startdown;
for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+1;daysadd:=daysadd+1) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;

As you will note I have amended the >time section to reflect the time the SLA finishes. An example call that I have has the log date/time as 24/10@15:23, the downtime was in the region of 10hrs 5minutes giving an expected result from the calculation above to be 25/10 @ 11:28 yet it has returned 27/10@17:27 and I have no idea why.

Regards
George

 

[mbarron]

One thing I noticed is that you changed this line:
if time((uptime1))>time(23,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)

This would throw off the formula. It no longer checks to see if the finish time is after 17:00. It now checks for 23:00. This means a down time of 4 hours could start a 16:00 and be completed at 20:00.

Your date of 24/10 is on a weekend - another thing the formula wasn't designed to handle.


If the start of downtime occurs on a weekend, the following formula will start the service time on the following Monday at 09:00. The servicing hours are assumed to be from 09:00 to 17:00.


local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate(({downtime.field})/8);
local numbervar down:=({downtime.field}) - (daysadd * 8);
local numbervar looper;
local numbervar addto;
local datetimevar downdate:={datetime.of.start.of.downtime};//start of down time
local datetimevar dtalter;

if dayofweek(downdate) in [1,7] then
(if dayofweek(downdate)=1 then downdate:=downdate +1 else
if dayofweek(downdate)=7 then downdate:=downdate +2;
dtalter:=datetime(year(downdate),month(downdate),day(downdate),9,0,0))
else dtalter:=downdate;



uptime1:= dtalter + down/24;
if time((uptime1))>time(23,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);
uto:=startdown;
for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+1;daysadd:=daysadd+1) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;






Mike

 

[gpwizz]

You are correct Mike, my original request was for a specific time window but I have now come across another that needs to be managed by a variation of your original formula. This particular example that I have mentioned has a service coverage of 9am to 11pm 7 days per week so if the log date/time was Friday at 8pm and the downtime was 4 hours that would make the resolution Saturday at 10am. So unless the downtime was over, using the above example and say the downtime was 18 hours (which is possible) would mean adding 2 days to the result, therefore Sunday 10am. Is this getting to the stage where it requires another post by me for a new formula?

 

[mbarron]

I need to fix the 9a to 5p formula. It starts having problems whith large downtimes.

Here is one for the 9A to 11P 7 days a week:
local numbervar down7:={test.down};
local datetimevar start7:={test.date};//start of downtime
local datetimevar end7; //output
local numbervar days7;
local numbervar hours7;
local datetimevar fix7; //"fixed" time


days7:=truncate(down7/14); //days down
hours7:=down7 -days7*14; //parts of a shift
fix7:=start7 + hours7/24;
if time(fix7) > time (21,59,59) then fix7:=fix7 +10/24 else
fix7:=fix7;

end7:=fix7+days7




Mike

 

[mbarron]

Revised 9 to 5 formula to handle larger down times.


local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate(({downtime.field})/8);
local numbervar down:=({downtime.field}) - (daysadd * 8);
local numbervar looper;
local numbervar addto;
local datetimevar downdate:={datetime.of.start.of.downtime};//start of down time
local datetimevar dtalter;

if dayofweek(downdate) in [1,7] then
(if dayofweek(downdate)=1 then downdate:=downdate +1 else
if dayofweek(downdate)=7 then downdate:=downdate +2;
dtalter:=datetime(year(downdate),month(downdate),day(downdate),9,0,0))
else dtalter:=downdate;



uptime1:= dtalter + down/24;
if time((uptime1))>time(17,00,00) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);
uto:=startdown;
for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+1;daysadd:=daysadd+1) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;


local datetimevar output:=uto +addto + daysadd;
if dayofweek(output)=1 then output:=output+1 else
if dayofweek(output)=7 then output:=output+2 else
output:=output

Mike

 

[gpwizz]

Mike, the closest I can get is your last formula. When the log date and fix time are within the same working day (9am to 11pm) the calculations are fine. When the resolution is the following working day (Monday to Sunday) it seems to lose 10 hours and put it before the working day start time. Below is the formula as I have used it.

local datetimevar startdown;
local datetimevar uptime1;
local datetimevar uto;
local numbervar daysadd:=truncate(({SCCall.Call_DTime})/8);
local numbervar down:=({SCCall.Call_DTime}) - (daysadd * 8);
local numbervar looper;
local numbervar addto;
local datetimevar downdate:={SCCall.Call_InDate};//start of down time
local datetimevar dtalter;

if dayofweek(downdate) in [1,7] then
(if dayofweek(downdate)=1 then downdate:=downdate +0 else
if dayofweek(downdate)=7 then downdate:=downdate +0;
dtalter:=datetime(year(downdate),month(downdate),day(downdate),9,0,0))
else dtalter:=downdate;



uptime1:= dtalter + down/24;
if time((uptime1))>time(22,59,59) then (startdown:=dateadd("h",-8,uptime1);daysadd:=daysadd+1)
else (startdown:=uptime1;daysadd:=daysadd);
uto:=startdown;
for looper:= 0 to daysadd do (
if dayofweek(uto)+looper in [1,7] then
(addto:=addto+0;daysadd:=daysadd+0) else (addto:=addto;daysadd:=daysadd));
uto + addto + daysadd;


local datetimevar output:=uto +addto + daysadd;
if dayofweek(output)=1 then output:=output+0 else
if dayofweek(output)=7 then output:=output+0 else
output:=output

Definitions example:
SCCall.Call_DTime = 7.38minutes, equates to 7hrs 22mins
SCCall.Call_InDate = 29th October 19:22 (downtime actually starts at this point)
With the example that I am referring to the "Fixed" time should be 30th Oct @ 12:45 but the formula returns 30th Oct @ 2:44.

Think I can sort the 1 minute but really can't locate the 10 lost hours.

HELP !!!

George

 

[mbarron]

It's not designed to handle the 9 to 11p time frame. That is why I supplied this one:

local numbervar down7:={test.down};
local datetimevar start7:={test.date};//start of downtime
local datetimevar end7; //output
local numbervar days7;
local numbervar hours7;
local datetimevar fix7; //"fixed" time


days7:=truncate(down7/14); //days down
hours7:=down7 -days7*14; //parts of a shift
fix7:=start7 + hours7/24;
if time(fix7) > time (21,59,59) then fix7:=fix7 +10/24 else
fix7:=fix7;

end7:=fix7+days7





Mike

 

[gpwizz]

Mike, getting some unusual results. My report returns 53 results. 28 of these have the start down date/time and resultant fixed date/time within the same working day and therefore return the correct value. 1 of these has a large value downtime of 28 hours 45 minutes and and calculates a fixed date/time correctly 2 days and a bit after the start down date/time. This is where the confusing bit starts. Of the 53 records, 25 are incorrect but with a trend, they are all the correct time - 10 hours. I have attached the results below so you can see for yourself (some numbers rounded up so may be 1 minutes difference)

Log Log Close Close Formula Formula Expected Expected
Date Time Date Time Fixed Fixed Fixed Fixed
Date Time Date Time
24/10 15:23 25/10 11:28 25/10 01:27 25/10 11:28
24/10 15:23 25/10 11:28 25/10 01:27 25/10 11:28
24/10 15:55 25/10 19:30 25/10 02:10 25/10 12:10
25/10 20:49 27/10 10:30 26/10 00:11 26/10 10:11
25/10 16:36 26/10 12:22 26/10 02:22 26/10 12:22
26/10 20:58 27/10 10:57 27/10 00:56 27/10 10:57
26/10 15:46 27/10 11:15 27/10 01:14 27/10 11:15
26/10 15:47 27/10 11:15 27/10 01:15 27/10 11:15
26/10 20:58 27/10 17:30 27/10 07:29 27/10 17:30
26/10 20:58 27/10 18:00 27/10 07:59 27/10 18:00
27/10 22:38 28/10 12:03 28/10 02:03 28/10 12:03
27/10 16:22 28/10 13:42 28/10 03:41 28/10 13:42
28/10 14:12 29/10 12:00 29/10 02:00 29/10 12:00
29/10 19:22 30/10 11:45 30/10 01:44 30/10 11:45
29/10 19:22 30/10 11:45 30/10 01:44 30/10 11:45
29/10 20:16 30/10 11:45 30/10 01:44 30/10 11:45
29/10 19:22 30/10 12:45 30/10 02:44 30/10 12:45
29/10 19:22 30/10 13:45 30/10 03:44 30/10 13:45
30/10 21:46 31/10 11:00 31/10 00:59 31/10 11:00
30/10 17:04 31/10 11:45 31/10 01:44 31/10 11:45
30/10 21:55 31/10 11:45 31/10 01:44 31/10 11:45
30/10 18:15 31/10 11:45 31/10 01:45 31/10 11:45
30/10 18:35 31/10 12:30 31/10 02:30 31/10 12:30
30/10 19:39 30/10 22:30 31/10 08:29 31/10 08:30
30/10 17:09 30/10 22:45 31/10 08:44 31/10 08:45

The formula I have is as follows.

local numbervar down7:={SCCall.Call_DTime};
local datetimevar start7:={SCCall.Call_InDate};//start of downtime
local datetimevar end7; //output
local numbervar days7;
local numbervar hours7;
local datetimevar fix7; //"fixed" time
days7:=truncate(down7/14); //days down
hours7:=down7 -days7*14; //parts of a shift
fix7:=start7 + hours7/24;
if time(fix7) > time (21,59,59) then fix7:=fix7 +10/24 else
fix7:=fix7;
end7:=fix7+days7

Any ideas?
George

 

[mbarron]

I think this time I got it right. The previous formula worked with my test data, but my test data was not as extensive and varied as it needed to be.

Give this one a test drive - it's the 9a to 11 p version):

local datetimevar downstart:={test.date};
local numbervar downtime:={test.down};
local numbervar hours:=truncate(downtime);
local numbervar minutes:=({test.down}-hours)*60;
local datetimevar outtime:=downstart;
local numbervar looper;
local datetimevar adjust;

outtime:= dateadd("n",round(minutes,0),outtime);
for looper:=1 to hours do (

outtime:=dateadd("h",1,outtime);
if time (outtime)>time (23,0,0) then
(adjust:=dateadd("d",1,outtime);
outtime:=datetime(year(adjust),month(adjust),day(adjust),9,minute(adjust),second(adjust))) else
if hour(time (outtime)) = 0 then
outtime:=datetime(year(outtime),month(outtime),day(outtime),10,minute(outtime),second(outtime)) else
outtime:=outtime
) ;

outtime


Mike