diff

[master_student]

Hello Guys,

I am trying to calculate the difference between two dates that have this format :10/25/2017 15:41:06 without counting holidays and sundays / saturdays

but the result seems wrong

bellow is the output :

here is my script :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

//****************************** Connection to CRMQVIEW *************************************//

LET vStartHour = 9;

LET vEndHour = 18;

LET vLunchStart =13;

LET vLunchEnd =14;

LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));

Holidays:

LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [

Holidays

20/03/2017

01/05/2017

25/07/2017

];

LET vHolidays = Peek('Holidays',0,'Holidays');

test :

LOAD

*,

             rangesum(Business_Hrs_Without_Overtime,Overtime) as Business_Hrs_With_Overtime;

LOAD *,

            rangesum(round(rangesum(

            NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // In between hours

            if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)),

            Rangemin(rangemax(frac(END_TIME),maketime($(vStartHour))),maketime($(vEndHour)))-

            Rangemax(rangemin(frac(END_TIME),maketime($(vStartHour))),maketime($(vStartHour))),0), // working hours last day

            if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),

            Rangemin(rangemax(frac(START_TIME),maketime($(vEndHour))),maketime($(vEndHour)))-

            Rangemax(rangemin(frac(START_TIME),maketime($(vEndHour))),maketime($(vStartHour))),0), // working hours first day

            if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If same day  then correct the hours

            )*24,0.01)) AS Business_Hrs_Without_Overtime,

           rangesum(if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) ,

           round(rangesum(if(frac(START_TIME)<maketime($(vStartHour)),maketime($(vStartHour))-frac(START_TIME),0),

           if(frac(END_TIME)>maketime($(vEndHour)),frac(END_TIME)-maketime($(vEndHour)),0))*24,0.01))) as Overtime ; // Overtime

LOAD *,

       ROW_ADDED_DTTM as START_TIME,

            ROW_LASTMANT_DTTM as END_TIME

;

select ...

any help pleasee

[stevedark]

Hi Wiem,

In what way does the result seem wrong?

I would look at unit testing each part of the process.  Does the variable for vWorkingHoursPerDay seem right, for instance.  Does MakeTime give the right result (it should be 1/24*vWorkingHoursPerDay).

NetWorkDays will always give you a result in whole days, so it is probably that you need to get the whole days as a value, multiple that by the hours per day, then add on number of hours until close of play on the start and then add on number of hours since start of play on the end date.  This then gets complicated if start or end time falls outside of working days.

My take on how to do this is included at the end of this blog post:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

This avoids the complexities of times though - so only gets you part of the way there.

Hope it's a pointer in the right direction though.

Steve

[master_student]

Hi Steve, Thanks for your reply. The result is 2.96, I didn't get what it means 2 hours 96 minutes ?

kush141087

any help please ?

Thanks both of you

[stevedark]

That will most likely be a decimal part of a day, i.e. 57 minutes short of three days.

To format ages, I have a variable called vFormatAge with the following content:

dual(num(floor($1), '#,##0') & 'd ' & time($1 - floor($1), 'hh:mm:ss'), $1)

You can then call that like this:

=$(vFormatAge(FileAge))

[master_student]

hello Steve,

I didn't get where to put your formula, here is my output :

[stevedark · Accepted solution]

Sorry, I misread and thought your decimal number was number of days, not hours.  The number will be hours and fractions of an hour.

The expression in the variable then only  needs to convert to hours before displaying as a time:

time($1 / 24, 'hh:mm:ss')

The result should then be given in hh:mm:ss format.

Hope that works out for you.

[master_student]

Thanks a lot stevedark‌