Calculating Days, Hours, Minutes, Seconds between two times 1

[CLKCLK]

I realized I posted this in the wrong section so I am reposting it here.

I am trying to calculate the DAYS:HH:MM:SS: between 2 timedate fields <startdatetime> - <enddatetime> = <tickettime>. (So if a ticket was opened on Friday at 4:00:00 pm and closed at 8:30:00 am on Monday the time elapsed would be 0 days 1 hour 30 minutes 0 seconds.)

The problem I am having is that I need to only calculate between our business hours M-F 8-5 and also need to eliminate weekends and federal holidays.

Any help would be appreciated. I looked at the Ken Hamady solution, but that is only calculating days. I need DD:HH:MM::SS.

Thanks

Coady

 

[lbass]

You were fine posting in the first forum, and I responded to you there last night. Didn't you check there before posting again?

-LB

 

[CLKCLK]

Yes, thank you, I saw your post and looked at that solution but it is for business hours only. I need days, hours, minutes, seconds between 2 datetime values minus weekends and holidays.

CLK

 

[lbass]

You should be able to adapt the formula easily enough to seconds, and then convert to days/hours/minutes/seconds.

-LB

 

[CLKCLK]

I also saw Ken Hamady's solution for the the DD:HH:MM:SS

WhilePrintingRecords;
NumberVar TotalSec := {YourTable.TotalSeconds};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;

Totext ( Days , '##' ) + ':' +
Totext ( Hours , '00' ) + ':' +
Totext ( Minutes , '00' ) + ':' +
Totext ( Seconds , '00' )

Is there a way to add an IF statement that counts 1 if the minutes are greater than 20?

 

[lbass]

Why do you want to count that?

And what exactly do you mean? If your data returned:

01:12:25:01
02:14:10:02
01:23:45:15

...what would you expect to see for your count?

-LB

 

[CLKCLK]

LB-

We have a service level agreement that requires that we respond to 80% of the calls within 20 minutes. I thought I would get a count of the total number of calls and then a count of all the calls over 20 minutes and see if we are making the 80%

I would expect to see a count of 3 as all those would be greater than 20 minutes.

Thanks-

CLK

 

[lbass]

You should just use a formula before conversion to the string that divides the seconds by 60 (without whileprintingrecords):

if seconds/60 > 20 then 1

Then right click on this formula and insert a sum at a group and/or report level.

-LB

 

[CLKCLK]

LB- Everything seems to be working now. My next problem has to do with business hours. I am using the Ken Hamady formula below, but our hours are going to change midway through the project so I wanted to add and if-else-then or a case statement or something to the formula to state if the startdate is between 11/20/09 and 11/23/09 then the business hours are 08:00 to 17:00 and if the startdate is between 11/24/2009 and 11/27/09 then the business hours are 06:00 to 18:00

WhileReadingRecords;
NumberVar Days := {@Business Days Formula}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"); // The start your work day
TimeVar SetEnd := TimeValue("17:00"); // The end your work day
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({End.Time}); // The data field that holds your End Time


If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)

Any ideas???

Thanks

Coady

 

[lbass]

WhileReadingRecords;
NumberVar Days := {@Business Days Formula}; // The field that calculates your business days
TimeVar SetStart;
TimeVar SetEnd;

if {table.startdate} >= date(2009,11,20) and
{table.startdate} < date(2009,11,24) then
SetStart := TimeValue( "8:00") else
if {table.startdate} >= date(2009,11,24) and
{table.startdate} < date(2009,11,28) then
SetStart := TimeValue( "6:00") else
SetStart := <yourdefaultstarttimehere>;
if {table.startdate} >= date(2009,11,20) and
{table.startdate} < date(2009,11,24) then
SetEnd := TimeValue( "17:00") else
if {table.startdate} >= date(2009,11,24) and
{table.startdate} < date(2009,11,28) then
SetEnd := TimeValue( "18:00") else
SetEnd := <yourdefaultendtimehere>;
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({End.Time}); // The data field that holds your End Time


If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)

Note that you should lay out your requirements in the first post in the thread, instead of tagging on new issues later on.

-LB

 

[CLKCLK]

LB- thanks, it works perfectly.

They just told me about the business hour change this morning.

thanks again for your help!

CLK

 

[JBC31979]

Do you have the code used to calculate the "Total Seconds"? Also, how does the code know the days?

Thanks,

 

[lbass]

Why don't you start your own thread and explain what you are trying to do? The "Total Seconds" above is shown as a field, not a calculation. Identify the fields you are working with, their datatypes, and show some sample data, along with an explanation of what the report should do.

-LB

 

[JBC31979]

I'm trying to do exactly what CLKCLK is doing and I'm just confused as to what is meant by Table.TotalSeconds.

Thanks,

 

[lbass]

If you have a field that holds total seconds, replace {Table.TotalSeconds} with that. Or you might have to replace it with a calculation, as in:

datediff("s",{table.datetime1}, {table.datetime2})

Can't help more than that without more information.

-LB

 

[JBC31979]

THAT's IT! Thanks so much!

 

[Cyked]

I found CLKCLK's post here after researching this. I found Ken Hamady's formula and am trying to work with it and I'm running into an issue.

I've set my business hours to be from 08:00 and 17:00 and anything that falls outside of that for start or end time I get a negative value.

So if I have a ticket opened at 8:50am and closed at 3:30pm (regardless of the day) I have no issues. If I have a ticket opened at 3:30am I get a negative value.

I'm running other formulas that are converting GMT to PST.

First I'm running this to get # of days between open/closed (minus weekends).


WhileReadingRecords;
Local DateVar Start := {@Begin Date}; // place your Starting Date here
Local DateVar End := {@Closed Date}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
//Local Numbervar Hol;
//DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

//Local NumberVar i;
//For i := 1 to Count (Holidays)
//do (if DayOfWeek ( Holidays ) in 2 to 6 and
// Holidays in start to end then Hol:=Hol+2 );

Weeks + Days

----------------------------------------------------------

Then I'm running this to get specifically the data providing business hours are 8-5.

WhileReadingRecords;

NumberVar Days := {@#daysbetween}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "08:00:00"); // The start your work day
TimeVar SetEnd := TimeValue("17:00:00"); // The end your work day
TimeVar StartTime := TimeValue({@PST_Opentime});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({@PST_Closetime}); // The data field that holds your End Time

//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM. Of course, this won't work if your workday is over 12 hours.
// If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
// If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)