Calculation of years weeks and date total working dates

[Anonymous]

Hi i have employees joining date,time and end date, time.

how to find total working days excluding Saturday,Sundays

Field like

Employee ID     DOJ              Date_Time    End_Date      End_Time

1001                 07/02/2015    10:35am       08/08/2018     9:50

[Anonymous]

Dear Than Mughi,

It will show like

EmployeeID	DOJ	Date_Time	End_Date	End_Time	Days	Years	Months	Weeks
1004	05/09/2014	06.00am	04/10/2016	10.00	0	-1Years+0Months	-1Months+0days	-1weeks+0days
1005	06/07/2015	07.00am	15/06/2018	09.00	0	-1Years+0Months	-1Months+0days	-1weeks+0days
1002	08/04/2015	09.56am	09/10/2018	10.00	0	-1Years+0Months	-1Months+0days	-1weeks+0days
1001	07/02/2015	10:35am	08/08/2018	9:50	0	-1Years+0Months	-1Months+0days	-1weeks+0days
1003	10/03/2016	11.20am	14/05/2017	11.00	0	-1Years+0Months	-1Months+0days	-1weeks+0days

[thannila]

why you are getting null values?.I have attached my sample file.It's working.check it

PFA,

[thannila]

Replace your qlikview main function with these following script.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='₹ #,##0.00;-₹ #,##0.00';

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

SET DateFormat='DD/MM/YYYY';

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

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-IN';

SET CreateSearchIndexOnReload=1;

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

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

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

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

SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Now execute the above mentioned code it will work.

[Anonymous]

hi its working thanks for it

EmployeeID	DOJ	Date_Time	End_Date	End_Time	Days	Years	Months	Weeks
1004	05/09/2014	06.00am	04/10/2016	10.00	543	2Years+1Months	25Months+3days	108weeks+6days
1005	06/07/2015	07.00am	15/06/2018	09.00	770	2Years+9Months	35Months+8days	153weeks+7days
1002	08/04/2015	09.56am	09/10/2018	10.00	915	3Years+5Months	42Months+7days	182weeks+6days
1001	07/02/2015	10:35am	08/08/2018	9:50	913	3Years+5Months	42Months+6days	182weeks+6days
1003	10/03/2016	11.20am	14/05/2017	11.00	307	1Years+2Months	14Months+3days	61weeks+3days

[thannila]

If it is correct mark this one as helpful and close the thread.