Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You have a function called NetWorkdays() that can calculate the net number of worling days for you:
NetWorkdays( DOJ , End_Date )
It can also handle holidays by adding them as a third parameter.
Please use the networkdays function
networkdays ('07/02/2015', '08/08/2018')
The networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.
Please use the below ink for more details.
i want years,months,weeks,days and hours
i want year,month,week,days and hours
What is start and end hour of the working day?
Try like this, to avoid loading of saturday and sunday.
Table1:
LOAD
Date,
Date(Date,'DDMMM') as Day,
Product,
Sales
FROM
(ooxml, embedded labels, table is Sheet1) Where Not Match(WeekDay(Date),'Sat', 'Sun');
And consider this, to avoid Holidays from date:
By joining/mapping if the HolidaysDate matched with date from calendar the calendar-table received the additionally fields or values. With both approaches you must make sure that the Holidaysdate will be regocnized as date and quite often to have the same format. Therefore it's recommended that all matching and calculations with dates are performed all numbers - num(date) ad NumDate.
Try this Below Code:
DataSource:
load * Inline
[
EmployeeID,DOJ,Date_Time,End_Date,End_Time
1001,07/02/2015,10:35am,08/08/2018,9:50
];
Data:
Load EmployeeID, Date(MinDate + IterNo() -1 ) AS date
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
EmployeeID,
Min(DOJ) AS MinDate,
Max(End_Date) AS MaxDate
RESIDENT DataSource group by EmployeeID;
Data1:
load *,weekday(date) as week
resident Data WHERE weekday(date)<>'Sat' and weekday(date)<>'Sun';
drop table Data;
left join(DataSource)
Data2:
load EmployeeID,count(week) as no.of.working.days
resident Data1 group by EmployeeID;
DROP TABLE Data1;
it shows like
EmployeeID | DOJ | Date_Time | End_Date | End_Time | no.of.working.days |
---|---|---|---|---|---|
1001 | 07/02/2015 | 10:35 | 08/08/2018 | 9:50 |
not use
if i have multiple records how to do?
Try this below code:
DataSource:
load * Inline
[
EmployeeID,DOJ,Date_Time,End_Date,End_Time
1002,08/04/2015,09.56am,09/10/2018,10.00
1001,07/02/2015,10:35am,08/08/2018,9:50
1003,10/03/2016,11.20am,14/05/2017,11.00
1005,06/07/2015,07.00am,15/06/2018,09.00
1004,05/09/2014,06.00am,04/10/2016,10.00
];
T1:
LOAD *,
NETWORKDAYS(DOJ,End_Date) AS Days,
FLOOR((End_Date- DOJ)/365 )&'Years'&'+'&subfield(num((End_Date- DOJ)/365,'0.0'),'.',2)&'Months' as Years,
Floor((Date(End_Date)-Date(DOJ))/30)&'Months'&'+'&subfield(num((Date(End_Date)-Date(DOJ))/30,'0.0'),'.',2)&'days' as Months,
Floor((Date(End_Date)-Date(DOJ))/7)&'weeks'&'+'&subfield(num((Date(End_Date)-Date(DOJ))/52,'0.0'),'.',2)&'days' as Weeks
RESIDENT DataSource;
drop table DataSource;