Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am facing problem to count workingtimes from StartDate to EndDate where the office hour from Time_Start='08:45:00' to Time_End='17:45:00'. I already try using the solution provided on others discussion in this forum but still not resolved.
Hope I can get the correct way on how to compute the WorkTimes below: -
a. Period from StartDate to EndDate;
b. Exclude Holiday;
c. time range is actual time from StartDate to EndDate between 08:45:00 - 17:45:00;
1. if StartDate or EndDate is less than 08:45:00, the time to be counted as 08:45:00;
2. if StartDate or EndDate is more than 05:45:00, the time to be counted as 05:45:00;
3. else to use the actual time for StartDate or EndDate;
4. for the date between StartDate and EndDate to be used time as 08:45:00 till 05:45:00 (9hours) - exclude Public Holiday.
set Time_Start='08:45:00';
set Time_End='17:45:00';
set Holiday='1/2/2018','8/2/2018','16/2/2018','20/2/2018','23/2/2018','25/2/2018';
DATA:
NoConcatenate
LOAD Distinct * Inline [
No, StartDate, EndDate
01135002201201819829, 23/01/2018 21:27:50, 13/02/2018 11:17:19
01135060602201804002, 09/02/2018 08:48:07, 27/02/2018 11:46:22
01135060902201808420, 12/02/2018 15:26:59, 27/02/2018 15:58:09
01135062002201813660, 20/02/2018 14:08:53, 27/02/2018 18:09:04
01135300102201800812, 02/02/2018 16:07:52, 15/02/2018 11:18:08
01135300102201800831, 02/02/2018 12:02:57, 06/02/2018 16:09:11
01135300102201800984, 07/02/2018 11:01:28, 12/02/2018 15:14:39
01135300201201800708, 03/01/2018 09:31:40, 05/02/2018 14:37:38
01135300202201801268, 06/02/2018 09:30:26, 08/02/2018 11:02:32
01135300202201801574, 05/02/2018 13:44:51, 21/02/2018 12:43:22
01135300202201801893, 14/02/2018 16:10:05, 27/02/2018 15:00:34
01135300301201801775, 03/01/2018 17:41:04, 01/02/2018 06:16:22
01135300402201802237, 05/02/2018 12:28:28, 27/02/2018 16:24:08
01135300502201802386, 06/02/2018 07:32:07, 12/02/2018 12:29:02
01135300502201802422, 07/02/2018 17:53:49, 13/02/2018 20:01:14
01135300502201802472, 06/02/2018 11:51:19, 14/02/2018 12:13:09
01135300502201802507, 06/02/2018 12:07:23, 22/02/2018 12:57:31
];
Work:
LOAD Distinct
*,
NetWorkDays(date#(left(StartDate,10),'dd/MM/yyyy'),date#(left(EndDate,10),'dd/MM/yyyy'),$(Holiday)) as WorkDays ,
NetWorkDays(timestamp#(StartDate,'dd/MM/yyyy jhh:mm:ss'),timestamp#(EndDate,'dd/MM/yyyy hh:mm:ss'),$(Holiday)) as WorkTimes
Resident DATA;
DROP Tables DATA;
Can you explain further on these conditions in relation to first row in your inline table? Also share the expected output.
1. if StartDate or EndDate is less than 08:45:00, the time to be counted as 08:45:00;
2. if StartDate or EndDate is more than 05:45:00, the time to be counted as 05:45:00;
Try Like below.. You could apply/Change the rules on top of the fields
Let Time_Start=Num(Time#('08:45:00','hh:mm:ss'));
Let Time_End=Num(Time#('17:45:00','hh:mm:ss'));
set Holiday='1/2/2018','8/2/2018','16/2/2018','20/2/2018','23/2/2018','25/2/2018';
DATA:
Load
No,
IF(StartTime#<=$(Time_Start),Timestamp(StartDate#+$(Time_Start),'DD/MM/YYYY hh:mm:ss'),ActualStartDate) As StartDate,
IF(EndTime#>=$(Time_End),Timestamp(EndDate#+$(Time_End),'DD/MM/YYYY hh:mm:ss'),ActualEndDate) As EndDate,
StartDate#,
StartTime#,
EndDate#,
EndTime#,
ActualStartDate,
ActualEndDate;
LOAD
No,
Floor(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss')) As StartDate#,
Frac(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss')) As StartTime#,
Floor(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss')) As EndDate#,
Frac(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss')) As EndTime#,
TimeStamp(Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') As ActualStartDate,
TimeStamp(Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') As ActualEndDate
Inline [
No, StartDate, EndDate
01135002201201819829, 23/01/2018 21:27:50, 13/02/2018 11:17:19
01135060602201804002, 09/02/2018 08:48:07, 27/02/2018 11:46:22
01135060902201808420, 12/02/2018 15:26:59, 27/02/2018 15:58:09
01135062002201813660, 20/02/2018 14:08:53, 27/02/2018 16:09:04
01135300102201800812, 02/02/2018 16:07:52, 15/02/2018 11:18:08
01135300102201800831, 02/02/2018 12:02:57, 06/02/2018 16:09:11
01135300102201800984, 07/02/2018 11:01:28, 12/02/2018 15:14:39
01135300201201800708, 03/01/2018 09:31:40, 05/02/2018 14:37:38
01135300202201801268, 06/02/2018 09:30:26, 08/02/2018 11:02:32
01135300202201801574, 05/02/2018 13:44:51, 21/02/2018 12:43:22
01135300202201801893, 14/02/2018 16:10:05, 27/02/2018 15:00:34
01135300301201801775, 03/01/2018 17:41:04, 01/02/2018 15:16:22
01135300402201802237, 05/02/2018 12:28:28, 27/02/2018 16:24:08
01135300502201802386, 06/02/2018 07:32:07, 12/02/2018 12:29:02
01135300502201802422, 07/02/2018 17:53:49, 13/02/2018 17:01:14
01135300502201802472, 06/02/2018 11:51:19, 14/02/2018 12:13:09
01135300502201802507, 06/02/2018 12:07:23, 22/02/2018 12:57:31
];
Work:
LOAD Distinct
*,
NetWorkDays(Floor(StartDate),Floor(EndDate),$(Holiday)) as WorkDays
//NetWorkDays(timestamp#(StartDate,'dd/MM/yyyy jhh:mm:ss'),timestamp#(EndDate,'dd/MM/yyyy hh:mm:ss'),$(Holiday)) as WorkTimes
Resident DATA;
DROP Tables DATA;