Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

[mdmukramali]

Dear All,

In my sample file i have two dates

1) TRANSFER_DATE

2)NEXT_TRANSFER_DATE

I am trying to calculate the difference between two dates in hours by excluding Weekends ( which are Friday and Saturday ) and non working hours on working days.

Working Hours : 8 AM to 4 PM..

i try tried using the networkdays but it's excluding Saturday and Sunday.

so please anyone help me to sort out the issue.

kindly find the attached sample file.

Thanks,

Mukram.

[Anonymous]

hi mohammed,

you can change the order of DayNames variable

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

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

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

instead of SETDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

and save application

or

Dual(Subfield('$(DayNames)',';',WeekDay(Date)+1),Mod(Date-1,7)) as WeekDay

and use Networkdays function

[maxgro]

I try to adapt a .qvw I posted here

Re: Calculate Elapsed Time Nett‌

see attachment

in the same post you can find some useful resoruces (Gysbert's answer)

[mdmukramali]

Dear Massuno,

Thanks for the excellent Solution.

but i have 500,000 rows of data .

which is taking too long time to reload the data. ( More than 3 hours)

any other way is possible to get the solution.

Thanks,

Mukram.

[Anonymous]

See if this thread helps you.Re: Urgent: calculating working hours from 2 date fields

You have of course to adjust to your weekend and working hours.

The latest version on the very bottom.

[mdmukramali]

Hi Michael,

I tried your solution but it's not excluding the Friday and Saturday as weekends.

Thanks,
Mukram.

[Anonymous]

Hi Mukram,

I never said it did, that's why it needs adjustments for your definition of weekends and start/end time.  As for the start/end - they are variables at the beginning of my script.  Weekend definition, I didn't try other than Saturday/Sunday, hope you'll figure out how to change it,

Regards,

Michael