Interval in DD HH:MM just excluding 'Sunday'

[#1]

Greetings all,

I have a small issue, I want to calculate the interval between 2 dates but excluding the Sunday, not all the weekend. So far I had been using the 'NetWorkDay()' function but this one is excluding Sunday and Saturday too. Saturday I want it in the calculation.

So the result that I want should be in Days Hours:Minutes (interval)

Anyone has any idea how I can do this ?

Thank you!

[swuehl]

You can look into one of the approaches shown here:

Calculate hours between two Date/Time strings

They may be a bit more complex than requested, but it should be quite easy to simplify and adapt.

[#3]

Yes, I know that post, but I did not find anything that can help on my situation.

[swuehl]

Do you actually have start and end dates or timestamps?

[#5]

Time stamps. Sorry that I did not mention it.

[#6]

But I have start time stamp and end time stamp.

[swuehl]

Here is adapted sample code from the referenced thread:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM  

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, frac(DT1), 0) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), frac(DT2),1) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP

where WeekDay(Date)<6 and not match(Date,$(vHol))  // Exclude Sundays and Holidays defined above

group by ID;

drop table TMP;

[#8]

Thanks! But there is any way to do this into a chart ? Not in the script.But somehow to exclude the sundays ? Like this example ?

=Interval(

          (RangeMin(frac(Closedate), MakeTime(18))

          - RangeMax(frac(Logdate), MakeTime(8)))

          + (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

[swuehl · Accepted solution]

Maybe like

Interval(

     rangesum(

          DT2-DT1,

          -If(Weekday(DT1)=6,1-frac(DT1)),

          -If(WeekDay(DT2)=6,frac(DT2)),

          -Div(floor(DT2)-floor(DT1),7), -If(WeekDay(DT2)<Weekday(DT1),1),  if(WeekDay(DT1)=6,1)

     )

)

edit:

added a component to correct Sunday counting

[#10]

Is not working properly.

This is my script where I calculate only the work days.

Interval(

          (RangeMin(frac(Real_Resolution_Provided_to_Client__c))

          - RangeMax(frac(Open_Date__c)))

          + (NetWorkDays(Open_Date__c, Real_Resolution_Provided_to_Client__c))

,'D hh:mm')

Do you have any idea in order to change this one to take the Saturdays ?